Pivot Tables, Grouping, and Calculations

[Mike]

I have daily sales data for the last four years. I have created a pivot table
that shows sales by client and have grouped the daily sales information into
months and years. I only want to display the last thirteen months worth of
infomation. I can this to work quite easily.

I also want to show the percenatge difference in revenue between months for
each client. I can do this with no problem. The problem is that I can not get
the calculation to work to show the % difference between January 2009 and
December 2008. That calculation shows nothing. The calculation for the %
difference between December and November works fine.

I think that the problem may have something to do with how I have grouped
the data. Does anyone have any suggestions on how to correct this/

 

[Shane Devenshire]

Hi,

I believe this occures because of the grouping, one solution would be to put
the calculating outside the pivot table. In the data area I have the
straight calculation of sales, not the % Difference from. For example, I have
this pivot table in the range E1:T14, I copy the month titles to F17 and the
Client names to E18 and down. I enter the year in F16 and to the right.
Where Nov 08 is in column F and so forth. In cell G18 I enter the formula:

=(GETPIVOTDATA("Sales",$E$1,"Client",$E18,"Month",G$17,"Years",G$16)-GETPIVOTDATA("Sales",$E$1,"Client",$E18,"Month",F$17,"Years",F$16))/GETPIVOTDATA("Sales",$E$1,"Client",$E18,"Month",F$17,"Years",F$16)

Note that this is a modified GETPIVOTDATA function inwhich I have replaced
hard coded values with cell references. Then I copy this formula down and
over. Be careful with the absolute cell references.