topic Re: Add a difference column to a Pivot table in QlikView

Add a difference column to a Pivot table

janwillem — Wed, 09 Nov 2011 12:13:14 GMT

Hi,

I'm trying to add a difference column to a pivot table but I can't get it to work.

I already try'd the example in the following discussion: http://community.qlik.com/message/88356

But I can't get it to work the way I want to. I need 3 columns: 2009, 2010 and a difference column.

I've added a simple version of the qvw file i'm using.

Can anyone help me?

Thanks.

Wed, 09 Nov 2011 13:19:38 GMT

You need 2 expressions in your case:

Remove the year as Dimension

Add two Expressions, one for each year set the year with Set Analysis:

Sum ({$ <Year={$(=max(Year))}>} Amount)

Sum ({$ <Year={$(=max(Year)-1)}>} Amount)

and at last column(1)-column(2) or in other direction

Sum ({$ <Year={$(=max(Year))}>} Amount) - Sum ({$ <Year={$(=max(Year)-1)}>} Amount) as third Expression

janwillem — Wed, 09 Nov 2011 13:35:31 GMT

Thanks Thomas. I hoped there was an easier way but this works great.

43918084 — Thu, 26 Mar 2020 15:31:52 GMT

I have similar problem and I use your suggestion. It does provide the difference but the

Sum ({$ <Year={$(=max(Year))}>} Amount) includes all data of Max Year. May I know how I can sum only criteria set in my alternative state?