Add a difference column to a Pivot table

[janwillem]

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.

[#2 · Accepted solution]

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

OR

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

[janwillem]

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

[43918084]

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?