1
I have an Excel 2010 PivotTable pointed to some data in the workbook that includes a date. I can group the date field into year and month just fine. What I want to do is produce a chart of a filtered date range (e.g. the previous 12 months). However, whenever I try to apply a "Between" date filter to the grouped date field (either the Year or Month grouping), my table blanks the rows of the filtered-out dates, but continues to display the row labels.
To illustrate, the chart of the table ends up looking like the following (showing blank columns for the filtered-out dates):
I control the underlying data; I can add additional columns to filter on, if necessary. So far, my only solution has been to delete all the data outside my desired date range and add separate columns for the month and year to act as row labels in the pivot table (i.e. I am no longer grouping on my date field). This seems like an unnecessary effort - surely someone else has this same requirement? What setting(s) am I missing?
No reason why it shouldn't work (it does for me). What does the pivot table look like when you've filtered out the dates? Does it have lots of blanks too? – Rhys Gibson – 2011-04-11T08:09:18.727
@Rhys Gibson - the pivot table likewise displays a blank row for each of those months in the chart above. – ropable – 2011-04-12T00:07:15.607
Then the problem is the pivot table, not the chart. The chart is simply displaying what's in the table. So if you can work out why your pivot is showing 0's or filter out the blanks, the chart should automagically fix itself. – Rhys Gibson – 2011-04-12T02:32:30.657