Has anyone ever come up with a solution to do a rolling 12 months sum or average that crosses years where the period and years are in different dimensions? The only thing I have come up with was a dynamic calc where the subvars set the end point and save off history. Would need this to be more like DTS. I suppose a CDF is in order.

http://docs.oracle.com/cd/E17236_01/epm.1112/esb_tech_ref/xrange.html

Search the board for this function -- there are a bunch of examples using this technique and others to do rolling sums, averages, etc.

One thing -- if you are doing this in Planning BegBalance is going to ruin your day.

Regards,

Cameron Lackpour

Hi

Similar to Cameron but I've done this a few times using @CURRMBRRANGE. It does take several members which may be a slight downside (depending how many metrics you want to do this for) but I've always managed to do it using dyn calcs, where the account dimension is dense, so not too painful and it avoids the BegBalance issue in Planning that Cameron alluded too.

So for example you want 12 months rolling Sales.
Create 2 members:
SalesYTD = @SUMRANGE(Sales, @CURRMBRRANGE(Period, GEN, 4, , 0));
SalesEOY = @SUMRANGE(Sales, @CURRMBRRANGE(Period, GEN, 4, 1, ));

This assumes a Planning style period dimension as follows (so using GEN 4 gets you only the months, ignoring BegBalance):
Period
-BegBalance
-YearTotal
--Qtr1
---Jan
---Feb
etc

The first formula calculates the YTD value (from the first period to the period selected), the second formula calculates the value for the remainder of the year (period immediately following the period selected to the last period).

Finally create your 3rd and final member to bring it all together:
"Sales(12mth Rolling)" = SalesYTD + @PRIOR(SalesEOY, 1, @LEVMBRS(Years, 0));

You may need to wrap this in an IF statement, e.g. if your last period is Dec you may want:
IF (@ISMBR(Dec))
"Sales(12mth Rolling)" = SalesYTD;
ELSE
"Sales(12mth Rolling)" = SalesYTD + @PRIOR(SalesEOY, 1, @LEVMBRS(Years, 0));
ENDIF

Obviously depending upon how you calculate your rolling 12 month you can tweak each of the formulae above (some companies include the period in the 12 months, others do it on a trailing basis so July's 12 month rolling is actually July->PrevYr to June->CurrYr


Hope this helps
Stuart