Difference between columns in a pivot table

[#1]

Hello,

I have a pivot table, with the Year in columns. I have 2 measures for each year. How can i have a third column to display the difference between the measures in these two years.

                 2012               2013              Difference

               Sales, profit     Sales, profit       Sales%, profit%

Product A  300    25         350    75            ??           ??

Product B  .................

Product C

Find attached a sample application.

Thank you in advance for some possible solutions.

[swuehl · Accepted solution]

Maybe like attached, using a subtotal column and secondarydimensionality() together with chart inter record functions.

[#3]

Ingenious solution Swuehl!!

Else the other solution i was contemplating was using alternate states for the years.

But its ofcourse not the same thing.

Thank you once again!

Regards

[Anonymous]

Do you have a qvf version of this? Thanks.

[Anonymous]

Hi, I need your help to sort columns on difference value. I set the expression like following and it is giving difference value but cannot sort dimension sorted on Highest and lowest value. Can you help how to apply sort on dimension based on difference of values.

if(SecondaryDimensionality()=0,

num(last(sum(Sales))/first(sum(Sales))-1 ,'#.00%')

, sum(Sales)

)

[Anonymous]

I applied rank function as difference value on sorting sometime not working if one period don't have any data.

So I changed Sort Express as following and set ascending in order and it work.

RANK(sum({<Year= {$(=max(Year))}>}Sales.NetAmount) - sum({<Year= {$(=max(Year)-1)}>}Sales.NetAmount))