TM1 Forum

Hello,

I am trying to develop a moving average formula for a cube with a year and month separated dimension and I am having a hard time trying to retrieve data from december, november, etc of the last year. I did the following things:

1. Develop a cube with last period posibilities (month and year for the past 12 months) and a moving formula in the budget cube.
2. Formulas like (1+2+3+4+5+6+7+8+9+10+11+12 last months)/12 (I need a 3 and 6 moving average also)
3. Very complex or very long feeding process

Does anybody knows a simpler way to calc moving averages in periods involving last year for a balance sheet?

Have you considered doing the moving average calculation in a separate cube with a combined month-year dimension and feeding the result back to the "reporting" cube with the separate month and year dimensions?

The moving average calculation could then be done using consolidations.
Eg. for each month you would need to set up a consolidation for your avarage
C Jun 2008 4 Mth Avg
Jun 2008 0.25
May 2008 0.25
Apr 2008 0.25
Mar 2008 0.25

In the "reporting cube" feed the "calculation cube
['balance'] => DB('Calc Cube', !Month | !Year, ..... ) ;

To make the rule easy in the calc cube best to have a month and year attribute rather than using SubSt (rules will also calc quicker)
['balance'] = N: DB('Reporting Cube', AttrS('MonthYear', !MonthYear, 'Month'), AttrS('MonthYear', !MonthYear, 'Year'), ... ) ;

In the reporting cube a simple lookup rule gets the moving average from the calc cube
['4 Month Moving Average'] = DB('Calc Cube', !Month | !Year | '4 Mth Avg', .... 'Balance' ) ;

This may seem a little convoluted but it is generally easier than writing a moving average rule in a cube with separate time dimensions and it will calculate faster than a complex rule as it uses consolidations for the bulk of the calculation.

Off the top of my head not knowing your exact cube structure of problem I think this should work! ... but in TM1 there is always another way to solve a given problem ...

Cheers,
Scott W

Thanks Scott

Sound good using that new dimension but i nthe first step before the feeder, how do i transfer data

the In the ['MONTHYEAR'] =N: DB('Reporting Cube', !Month | !Year, ..... ) ; I got lost on how to do that with different year month dimensions

Done ... with Attributes..

I have to create to many consolidations to do it in that way i am going to make this idea as a module to the budget, make avg available for many accounts. with the monthyear dimension idea you provided thanks!

Glad you could figure it out.

You're right in that this method does require a fair amount of setup as a unique consolidation for each month is needed (or multiple consolidations for each month depending on what else you want to calculate.) It's a bit of work to set up but can be automated by TI or done in an XDI setting up for one year then copy +find/replace to propagate to other years.

The benefits are in calculation speed vs. a pure rule calculation and the fact that the consolidations are "nil overhead" vs. rules/feeders.

Cheers,
Scott W

I've done this a number of times without stepping away from the 2 lump (Year separate from Month) time structure. It's not too bad, but you need a cube for the workings.

The approach is:
12 Month Moving Average =
Current YTD
plus
Prior full year
less
Prior year YTD

Drop those into a workings cube, put the + and - into a hierarchy, tick done.

Feedback welcome.

Hi David,

Rather than FY - YTD for the prior year why not define a YTG (year to go) for each month as well as YTD?

Interesting idea. I often find it necessary to have YTG (though I haven't been calling it that) if I am feeding from a YTD-based cube to a current month-based cube, and it would be quite neat to pick it up for the moving average. But if that isn't required I think I'd stay with the FY less YTD as I'd have both and the workings (subtraction, no rule needed) are simple.

This moving average seems to have a lot of ways to be done, but must of them seems to be specific (for example only one possibility like a 3 month average).

To solve this, I decided to build a "some balance sheet account forecasting cube" with the !monthyear approach provided by scott, because I needed account forecasts using a 3, 6 and 12 moving average, a moving average formed by a monthly change percentage for the last six months, and a forecast based on a 12 months approach that determined a % to be applied. My formulas work perfect, because i only have to write them and feed them once (doesnt matter they are long, they are working!), but the hellish part is that i have to make 2 DB's (in and out between cubes) and the concerning feeders for each balance account i want to forecast. I also believe is a good answer because i have 10 companies with the same formula.

I dont like a lot this solution, probably i can do some reengeneering in the way I send accounts between cubes, but MOVING AVERAGE and SUMs formulas seems to be a tough twinkie to eat in !year and !month approaches.

Enlight me, please!!!

Regards,

maybe a bit complicated (did it after a course for TM1 starters), but worked with me.