Pivot Table

[Andrea]

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

 

[Barb Reinhardt]

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.

 

[Andrea]

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....

 

[Debra Dalgleish]

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.

 

[Andrea]

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