Avg 3 Month Relative Measure

[CognosChas15]

 ???

I was wonder if it is possible within Cognos Transformer to Create a Measure that would be based on 3 month avg pending on what ever Month/Year was selected from Time Dim?  For Example:  If you where to use 04/2007 on a report with this Measure, it would calculate an avg on: 01 -03/2007, if you used 05/2007 then the measure would represent 02-04/2007.   So it would be relative to what ever Date or Dates are used on the Report or Chart.  Any Ideas?

Thanks

Charlie

[MFGF]

Hi,

All the relative time features in Transformer are relative to the 'Current Period' defined in the data.  They are not designed to work differently on a report-by-report basis as the current period is defined each time the cube is refreshed (or hard-coded as a fixed point in time).

To achieve the results you're looking for, you'd need to be using Report Studio in Cognos 8 (if you have it), and create a calculated item that prompts for the month, uses the lastPeriods function to grab the last 3 months upto and including the prompted for month, then averages the current measure value across the three months in this set:

average(currentMeasure within set lastPeriods(3,[your month level] -> ?monthparam?))

If you don't have C8 Report Studio, you're going to struggle to achieve this.

Regards,

MF.

[CognosChas15]

Thank you for the Reply MF,  This helps confirm what I was telling our development group here regarding being able to do this within Transformer.  I have passed the note onto our Report Developer, who is working with Cognos 8.2 Report Studio for review. 

Thanks again! 

Charlie

[MFGF]

You're welcome!  :)

[Cannondale23]

one approach you can use is build up special time categories in your time dimension (say roll 3 for each month of year, so current, then create n-period running total = 3 so since its Dec you would get DEC, NOV, OCT) then current -1 create roll 3 offset -1 (you get NOV, OCT, SEP) etc all the way to current -12)
then you can create calculated categories using the avg functions off the base categories created.
but like the other guy said it's driven off current period so things will slide each month