Calculated column on Horizontal Pivot

[rwunderlich]

This one should be easy, I just can't find an example. I want to create a table like the following. Expressions on the vertical, one dimension (Year) on the horizontal. I want the Diff column to be the 2008 column - the 2007 column. I will have an arbitrary number of metrics (Budget, Sales) etc. I haven't been able to find an example that is a single table, but I beleve I've seen it done. Any ideas?

Year 2007 2008 Diff
Budget 800 2500 1700
Sales 1000 2000 1000

Thanks,
Rob

[#2 · Accepted solution]

Hi Rob,

We can use the total field to display the diff by using rowno()=0 to identify the total column and set analysis to compare the dates.

Here is the expression I used for Budget...

if(rowno()=0, sum({$ <Year={$(=max(Year))}>} Budget) - sum({$ <Year={$(=max(Year)-1)}>} Budget), sum(Budget))

-Andy

[Oleg_Troyansky]

I was going to suggest synthetic dimensions, but the idea to use Total for this purpose is simply brilliant!

[Anonymous]

(Copying Oleg )
I was going to suggest using total, but didn't think of using rowno(). My typical "condition of choice" is dimensionality. I think it would be dimensionality2 in this case.
Now Rob has a lot of options...

[rwunderlich]

Thanks Andy. That's a great solution.

-Rob