topic Re: Difference between columns in a pivot table in QlikView

Difference between columns in a pivot table

Tue, 17 Sep 2013 12:31:46 GMT

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.

Re: Difference between columns in a pivot table

swuehl — Tue, 17 Sep 2013 12:50:24 GMT

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

Re: Difference between columns in a pivot table

Tue, 17 Sep 2013 13:00:28 GMT

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

Re: Difference between columns in a pivot table

Anonymous — Mon, 26 Feb 2018 05:47:40 GMT

Do you have a qvf version of this? Thanks.

Re: Difference between columns in a pivot table

Anonymous — Mon, 08 Oct 2018 09:25:43 GMT

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)

)

Re: Difference between columns in a pivot table

Anonymous — Tue, 09 Oct 2018 16:53:04 GMT

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