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

[stevelord]

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

[#2]

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

[stevelord]

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.