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

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

    Steve Lord

      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:

      ClientUserIdTestYear20122013Change
      ClientA206 22No Change
      ClientA212 11No Change
      ClientA226 12Increase
      ClientA228 21Decrease
      ClientA207 -2One Value
      ClientA351 -1One Value
      ClientA225 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