Calculating YoY Growth

[gazoller]

Goal: Calculate YoY growth by FQ in a crosstab report
Formula: (New Fiscal Quarter Rev - Old Fiscal Quarter Rev) / Old Fiscal Quarter Rev
Required Result: The result will be data points of multiple years/fiscal quarters
E.g.
For Q1FY07 to Q1FY06 the result is 18%
For Q2FY07 to Q2FY06 the result is 13%
etc.
Where the data points are 18%, 13%, etc.

I have a measure for Fiscal Quarter but I don't understand how to tell Cognos to use the current FQ value and then also the (current FQ value - four quarters) for use in the calculation. Any pointers? Are there predefined YoY, YoY by FQ, and FQ over FQ functions?

Thanks
Garth

[wrightr]

Assuming current quarter is an integer from 1 to 4, try this filter for Current Quarter:

[Date] between

CASE [Cur Qtr]
  WHEN 1 THEN 2008-01-31
  WHEN 2 THEN 2008-04-01
...
END

and

CASE [Cur Qtr]
  WHEN 1 THEN 2008-03-31
  WHEN 2 THEN 2008-06-30
...
END

Does that help?

Riley

[gazoller]

Thanks for the reply, Riley.  I would like to avoid hard-coding specific dates as the end user could select dates from an unknown number of years. Perhaps I can somehow combine what you're doing here with currentMember as it passes through each iteration.

Cheers
Garth

[MFGF]

Hi,

Assuming you are using a dimensional package (your reference to currentMember suggests this), try using the parallelPeriod() function to get hold of the corresponding quarter from the previous year.

Your expression will look similar to this:

parallelPeriod([Year level],1,[Your current quarter member])

Does this help any?

Regards,

MF.