Pivot Table year vs. year - 1 etc.

[#1]

Hi,

I have a pivot table withrevenues for several years 2006 to 2008.

I'd like to calculate in a vs. Row the difference between the "current colum year" and the previous year.

Sth. like sum(Revenue) - sum(if(Year = Year-1, Revenue)), tried with

Sum({$<Year={$(=Only(Year)-1)}>} Revenue)

but don't get it working.

Please see example.

Anyone an idea?

Thanks in advance

[#2]

I think it is not possible to do a set analysis on dimensions.. The closest solution is attached in the message.

[#3]

i'd be also quite happy if it would work without set analysis 😉

Isn't there a way to handle this with "sum year" and "sum year-1"?

[stephencredmond]

Hi,

In a pivot table you can use Before and Above to perform inter cell calculations. So, assuming that Year is pivotted across the top I would have one expression that has:

Sum(Sales)

and the next has

Sum(Sales)-Before(Sum(Sales))

Very simple, no?

Regards,

Stephen

[#5]

Thanks Stephen. That's what I was looking for. Great 🙂