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

# 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

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.

• ###### Calculate a percentage change

I'm using

=only({\$<QUARTER={"\$(Variable2)"}, Year=>} CREDIT_SCORE)

to attempt to display the value for the previous quarter. This is not returning any values. I think this may be because there are multiple years it does not know which quarter to display. Hopefully this makes sense.

• ###### Calculate a percentage change

Shannon,
As i said, it depends on the format of the quarter you're using. If it is something like Q1, there is no information about the year, as you say. In this case you need additional condition for the year.
If it is something like Q1-2011, it should work.