Get comfy, this will take a while to explain 
For a while now, I've been grappling with finding a way to chart some data for a rolling 12 month period, using a Pivot Chart. For example, its now March 2011, and I need to produce a report showing number of widgets sold in Feb 2011 and the previous 11 months. At the beginning of April, I'll need to shift that date range to Apr 2010 - Mar 2011.
The data gets grouped by month and year. Important to note, however, that some months have no data. This causes gaps in the chart. No problem, I thought, I'll just tick the pivot table field setting 'Show items with no data'. But this then included a row for every single month in my source data. The date filter got ignored. (Why, Excel, why? Why not just show a zero for the 'blank months' in my date range, instead of ignoring the date filter altogether?)
So then I decided to add a column to my source data table which created a little year/month string based on the date, i.e. =TEXT(H2,"yyyy/mm"). I use that instead of the date column (H) and that works fairly well. Except that every month I have to change the selection, and I have a lot of these charts so it gets tedious.
What I wanted to do was have the charts use the correct rolling 12 month data automatically. So I added another column to the data table to flag each data row with 1 or 0 using the formula =IF(H2<DATE(YEAR(NOW()),MONTH(NOW()),1), IF(H2>DATE(YEAR(NOW())-1,MONTH(NOW()),0),1,0),0). In my pivot table I can now page filter on this column and voila, problem solved!
But no.... When I went back and changed the charts to use the new page filter and the original date column, instead of the artificial "yyyy/mm" column, I noticed I had gaps in the chart again for months with no data. And as soon as I ticked the field setting option 'Show items with no data' I was back at square one, namely having all the months from the source data instead of the specified date range. Dammit. I work on the first floor, so throwing myself out the window wouldn't even end my torment.
The combination of date filter, grouping by month & year, months with zero data and 'Show items with no data' seems to cause a perfect storm in Excel pivot charts. Phooey.
It seems like such a small thing to ask: All I want is to have a pivot chart which shows me the previous 12 months data, with zero values in the chart instead leaving out those months entirely, and it updates itself based on the flag column instead of me fiddling around with date selections every month. I thought that's quite a modest request Anyone have any suggestions on how I can actually do this?
Thanks for listening.
Sven
For a while now, I've been grappling with finding a way to chart some data for a rolling 12 month period, using a Pivot Chart. For example, its now March 2011, and I need to produce a report showing number of widgets sold in Feb 2011 and the previous 11 months. At the beginning of April, I'll need to shift that date range to Apr 2010 - Mar 2011.
The data gets grouped by month and year. Important to note, however, that some months have no data. This causes gaps in the chart. No problem, I thought, I'll just tick the pivot table field setting 'Show items with no data'. But this then included a row for every single month in my source data. The date filter got ignored. (Why, Excel, why? Why not just show a zero for the 'blank months' in my date range, instead of ignoring the date filter altogether?)
So then I decided to add a column to my source data table which created a little year/month string based on the date, i.e. =TEXT(H2,"yyyy/mm"). I use that instead of the date column (H) and that works fairly well. Except that every month I have to change the selection, and I have a lot of these charts so it gets tedious.
What I wanted to do was have the charts use the correct rolling 12 month data automatically. So I added another column to the data table to flag each data row with 1 or 0 using the formula =IF(H2<DATE(YEAR(NOW()),MONTH(NOW()),1), IF(H2>DATE(YEAR(NOW())-1,MONTH(NOW()),0),1,0),0). In my pivot table I can now page filter on this column and voila, problem solved!
But no.... When I went back and changed the charts to use the new page filter and the original date column, instead of the artificial "yyyy/mm" column, I noticed I had gaps in the chart again for months with no data. And as soon as I ticked the field setting option 'Show items with no data' I was back at square one, namely having all the months from the source data instead of the specified date range. Dammit. I work on the first floor, so throwing myself out the window wouldn't even end my torment.
The combination of date filter, grouping by month & year, months with zero data and 'Show items with no data' seems to cause a perfect storm in Excel pivot charts. Phooey.
It seems like such a small thing to ask: All I want is to have a pivot chart which shows me the previous 12 months data, with zero values in the chart instead leaving out those months entirely, and it updates itself based on the flag column instead of me fiddling around with date selections every month. I thought that's quite a modest request
Anyone have any suggestions on how I can actually do this?Thanks for listening.
Sven
