Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Pivot table summurizing data where years and quarter are in the row
label and average af amount in Values:
Values
Years Date Average of Amount $2 % diff previous Q
2005 Qtr3 $283.96
Qtr4 $344.83 21.43%

2006 Qtr1 $462.70
Qtr2 $603.55 30.44%
Qtr3 $665.53 10.27%
Qtr4 $674.41 1.33%

2007 Qtr1 $665.95

I run % difference from (previous), but i can't get say Q1 2006 % Difference
from the previus Q (Q4 2005) and the same for every Q1 with respect of the Q4
of previous year, all the rest of course works fine. Any hints?
thanks
Andrea
 
Look at your equation for Q1 for each year. I'm betting it's assuming the
"previous quarter" is in the blank row above it.
 
yep! but even if i remove the blank row no way!
Consider quarter and years are created automatically grouping a date field
like 01/01/2006 (if it can help). i have been around this "bug" for two years
and i thought eventualy excel 2007..... but nothing. it seems pivot table
consider every years as a block so before Q1 there is nothing....
 
You could add a column to the source data, and calculate the year and
quarter, e.g.:

=YEAR(A2) &"-"& VLOOKUP(MONTH(A2),$Q$2:$R$13,2)

Add that field to the pivot table, and use it as the base field for the
custom calculation.
 
yes! this is my work around. But i wonder if there is any way to do it in the
Pivot Table "environment" maybe using VBA.
thanks a lot anyway
 
Back
Top