2 Replies Latest reply: Jun 25, 2013 11:36 AM by Steve Lord

# Year 2 value - Year 1 value in a pivot table?

Hi, I have a pivot table with the following expression:

If(rbBMI='Normal Weight (18.6-24.9)' AND Count(Aggr(Max(TestDate), ClientAccountName, UserId, TestYear, TestMonth))<>Null(), Dual('1',1),

If((rbBMI='At Risk, Overweight/Obese (>24.9)' OR rbBMI='At Risk, Underweight (<18.6)') AND

Count(Aggr(Max(TestDate), ClientAccountName, UserId, TestYear, TestMonth))<>Null(), Dual('2',2)))

It basically gives a 1 if a BMI is in normal range, and a 2 if a BMI is outside normal range.

I want to be able to subtract year 1 from year 2 to find the change.

I want something like what you see in the Change column below:

 Client UserId TestYear 2012 2013 Change ClientA 206 2 2 No Change ClientA 212 1 1 No Change ClientA 226 1 2 Increase ClientA 228 2 1 Decrease ClientA 207 - 2 One Value ClientA 351 - 1 One Value ClientA 225 1 - One Value

I've got the 1s and 2s in place based on the risk range, I just need help getting the year over year change expression for QV.  Something in script would be great, but I'll take what works.

-Steve

• ###### Re: Year 2 value - Year 1 value in a pivot table?

try  with =Column(2)-Column(1)

• ###### Re: Year 2 value - Year 1 value in a pivot table?

Excel is that simple. Not so much Qlikview. ☺ (I did try below and variations to ensure I wasn’t overlooking an easy solution though.)

Qlikview might need some set analysis type formula for me to look at value in one state (i.e. column1 or testyear=2012) and value in another state (i.e. column2 or testyear=2011) and subtract one from the other.