Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Difference between columns in a pivot table

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Difference between columns in a pivot table

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

5 Replies
MVP
MVP

Re: Difference between columns in a pivot table

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

Not applicable

Re: Difference between columns in a pivot table

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

pfelicita
New Contributor

Re: Difference between columns in a pivot table

Do you have a qvf version of this? Thanks.

devoasqv
New Contributor

Re: Difference between columns in a pivot table

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)

)

devoasqv
New Contributor

Re: Difference between columns in a pivot table

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

Community Browser