Cumulative balance in TI or Rule

Post Reply
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Cumulative balance in TI or Rule

Post by Analytics123 »

Hi ,

I have the below scenario and would like to calculate a new measure which will do the cumulative total .

Region 1 Days Daily H0 Cumulative H0

1 120 120
2 100 220
3 150 370
4 200 570
5 33 603
6 50 653


I am expecting the calculation for Cumulative H0 based on my daily h0 based on days .

Can you tell me how can i achieve this .

Thanks,
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Cumulative balance in TI or Rule

Post by Steve Rowe »

Several ways come to mind.

Have a cumulative day consolidation in your dimension, this could be built in a straight forward way with a TI.
Your cumulative HO measure could then reference this in a rule.
[Cumulative HO] = N: DB ( ......'Daily HO' , !Day | 'Cumulative'....);
i.e. !Day | ' Cumulative' evaluate to Day 20 Cumulative at run time, "Day 20 Cumulative" being the sum of Days 1 to 20.

or do it all rules using a something like this, expect to have issues with performance and feeder stacks if you go down this road. Though the quickest to write I wouldn't recommend this in any system of a reasonable size.
#Todays total plus yesterdays cumulative
[Cumulative HO] = N: [Daily Ho] + DB ( ......'Cumulative HO', Attr ('Day' , !Day 'Prior')....);

Or calculate it in the TI process when you load the data in the first place, probably best for performance.

The choice is yours!
Technical Director
www.infocat.co.uk
Analytics123
Posts: 128
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Cumulative balance in TI or Rule

Post by Analytics123 »

I was able to do the consolidated element in the dimension itself .

How do I calculate it in the TI process when you load the data in the first place, probably best for performance ?

Should I do a while lopp or if loop for each day and sum the values and then store it , consolidation on dimension looks easier and better right?

Thanks
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Cumulative balance in TI or Rule

Post by Steve Rowe »

For the TI a lot will depend on how your data arrives and if you only do single day at a time and it arrives in time order.

If you can be sure you are processing daily data in day order then your TI can just take todays total, yesterdays cumulative, sum them and add it to todays cumulative.

For me this is a bit of risky build, if it goes wrong because the days arrive out of order you are a bit stuffed.

I'd actually have a second TI called after the main data load that was capable of performing the above logic for a range of days. The main data load could flag the days that need to be refreshed, this could in turn be used to build an ordered subset which would drive the processing and calculation of the days.
Technical Director
www.infocat.co.uk
Post Reply