Rules for Cumulative running Totals

[iansdigby]

Hi All,

Does anyone have an examples of a rule/s which will calculate a cumulative running total based on individual periodic data points?

e.g.
Actual hours (raw data): Mon: 5; Tue: 5; Wed: 0; Thu: 6
Cumulative (TM1 rule): Mon: 5; Tue: 10; Wed: 10; Thu: 16

It seems like something which lots of people must want to do but i haven't found any examples anywhere.

Much gratitude to anyone who can help.

Ian Digby

[David Usherwood]

I wouldn't use a rule at all, but a chart:
Mon -> TueCum
Tue -> TueCum
Wed -> WedCum
TueCum -> WedCum
etc
Charts are much quicker than rules (x100 per Paul Simon) and don't need feeders.

[iansdigby]

David,

I really appreciate your reply, but am mystified as to what kind of chart we are talking about? Do you mean a chart in TM1 WEb, or and Excel chart?

Regards

Ian

[David Usherwood]

Sorry - dimension hierarchy or rollup. In general, don't use rules to do + or - . Dimensions do it better.

[iansdigby]

David,

Now I getcha and that is so, so helpful and sure to solve the issue.

Heartfelt thanks

ian

[dan.kelleher]

Hi,

Whilst that works for a single time dimension, I have 2 dimensions representing time, a year {2009,2010,..., 2020}, and a period {01,02,...,12} and need to accumulate from Jan 2009 to Dec 2020.

The way I'm thinking of approaching this problem is to use a business rule to say that cum period 01 for say 2010 = cum period 12 of 2009 plus addition of period 01 values for 2010, and then use the method described above. The issue with this is that it's not scalable. I.e. I have to explicitly write this rule for each year I have.

Has anyone had experience with this before and am I correct in thinking this is the only way, or is there a more scalable solution? I am just thinking what would happen when the system owner (who has no rule editing experience) wants to add a new year to the years dimension.

Thanks,

Dan

[Steve Rowe]

Dan,
It's fairly common practice with multi-dimensional time systems to have a lookup cube that does all the period mapping for you.

Your PeriodLoopkup cube would be dimensioned something like this

Periods, Years, Time Unit (with numeric elements year and period), Time Measure (with string elements Prior and Next). You should populate this cube manually.

Your rule then becomes something like

Skipcheck;

[2009, 01, Cumulative]=N: [ value ];

[{2009,2010,..., 2020}, {01,02,...,12} ,'Cumulative']=N:
['Value'] +
Db ( Cube , !DIm1, DB ( PeriodLookup, !Year, !Period, 'Year','Prior' ), !Dim3, DB ( PeriodLookup, !Year, !Period, 'Period','Prior' ) , 'Cumulative');

Feeders;

['Value']=>['Cumulative'];
['Cumulative']=>Db ( Cube , !DIm1, DB ( PeriodLookup, !Year, !Period, 'Year','Next' ), !Dim3, DB ( PeriodLookup, !Year, !Period, 'Period','Next' ) , 'Cumulative')

(Note that it may be preferable to break the sequence artificially to be improve the performance, since you can end up with a very long chain of calculations.)

((You should be OK since you 20*12=240 combinations, but watch out as you might blow the feeder stack as I think you can only trigger 255 feeders from a single cell. The population of 2009, 01 triggers a chain of feeders 240 steps long. ))

HTH

[highroller]

Hey guys ... I am new to TM1 and I am trying to replicate my balance sheet in TM1. What I am having a problem doing is creating a column for the YTD balance with is a sum of the balance of all the periods up to where you are.

I have tried a million ways and in the latest iteration I am getting an N/A in the YTD field. I have tried to follow the advice in this post trying to make YTD a consolidation of the periods in the rules but have not been able to get this to work. I would really appreciate if someone can teach me how to create a running balance step by step or see what I have done and tell me what I am missing.

I have also added an image to show what I am getting and how I have the measure dimension setup.

Screenshot

TM1.jpg (94.72 KiB) Viewed 11347 times

This is how i have the rule setup to calculate YTD ---> ['YTD']=ConsolidateChildren('PeriodBalance');

Any help is greatly appreciated!

btw I am running TM1 9.4.1

[paulsimon]

Hi,

I would avoid consolidatechildren. Is there any reason why you cannot use consolidation in the dimension to give you YTD, as per the previous posts?

If the issue is that you have balances rather than movement figures, then a lookup cube that gives the last element in each consol could be what you are after, eg for Q1 it is Month 3, for H1 it is Month 6. The rule will need to work at all levels ie no N:

Regards


Paul Simon

[highroller]

hey I was able to get it done on the dimension ... this post helped me understand what I needed to do http://forums.olapforums.com/viewtopic.php?f=3&t=1936 ... thanks a lot for the reply Paul.