3 Replies Latest reply: Apr 17, 2011 1:08 PM by Michael Solomovich RSS

    Calculate a percentage change

      Hi,

       

      This is what I'm trying to do:

       

      I have a straight table where the dimensions are customer number and name. For the expressions I am trying to show the credit score for this quarter, the credit score for the last quarter and the % change.

       

      I have 2 variables. Variable1 is Quarter, Variable 2 is Quarter-1. I also have a list boxes for the quarter and year which the user will use for filtering.

       

      The first expression is pretty straightforward. I have ='QTR '&Variable1 for the Label and CREDIT_SCORE for the definition. For the second expression I have ='QTR '&Variable2 but I'm unclear on what to put for the definition so that it will display the value from the previous quarter (the label displays correctly). Also, I need to create a % change expression. It essentially needs to be calculated as Q4 CREDIT_SCORE - Q3 CREDIT_SCORE/Q3CREDIT_SCORE * 100. I'm sure that this requires set analysis although I'm not sure of the syntax.

       

      Any guidance would be greatly appreciated.

        • Calculate a percentage change
          Michael Solomovich

          Shannon,
          That's right, set analysis will be useful here. Syntax for the curent and previous quarter expressions may be like this:
          only({$<Quarter={"$(Variable1)"}, Year=>} CREDIT_SCORE)
          only({$<Quarter={"$(Variable2)"}, Year=>} CREDIT_SCORE)
          (Details may vary depending on your Quarter field and Variables formats)
          The Year= part is to make the result independent from Year selections, otherwise you'll have a problem is current quarter is Q1.