Page 1 of 1

Cumulative balance in TI or Rule

Posted: Wed Jan 10, 2018 2:44 pm
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,

Re: Cumulative balance in TI or Rule

Posted: Wed Jan 10, 2018 5:29 pm
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!

Re: Cumulative balance in TI or Rule

Posted: Wed Jan 10, 2018 10:30 pm
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

Re: Cumulative balance in TI or Rule

Posted: Thu Jan 11, 2018 9:42 am
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.