Hi all,
I have an Excel 2010 pivot chart that is taking external data from Access 2010, summing a volume, and displaying it by month. I have grouped the data beyond the dataset so that I can show the whole year, even though there are no records with sales in Sep-Dec.
My question is whether it is possible to hide the datapoints after the previous month, e.g. we are in Sep so everything from Sep-Dec should not appear as a datapoint. Currently the pivot chart sums these months to 0, and from Aug to Sep there is incorrectly what looks like a sharp drop in sales to 0.
The complication is that I can also have sums of 0 in previous months, so I cannot identify the datapoints I want to remove by checking if they are 0 or blanks. I need some way of checking if the displayed month is greater than or equal to the current month. Unfortunately you cannot use a variable function (such as NOW) in a calculated pivot field.
Does anyone know (or want to try) a way to get around this? See below image for clarification.
pivot_hide.PNG
Cheers!
Bookmarks