Dynamic YTD Calculation on a Continuous Time Dimension

[WayneBo]

Hi All TM1 Experts

I have a question regarding how to do a dynamic YTD calculation on a continuous time dimension (not a Discrete Time Dimension).

Below are the details.

So, I have a daily labour hour cube, where using a continuous time dimension (ie, 01/07/2019, 02/07/2019, 03/07/2019 .... etc (dd/mm/yyyy)).
This cube is storing the labour hours used each day.

I want to do a maybe rule based formula, where a measure (say YTD Hours) is calculated total sum of labour hours from 1st day of financial year (01/07/2019, dd/mm/yyyy, in Australia) to whatever the current date is today ( ie something like =Today() ).

The structure of my Continuous Time Dimension is, Year -> Quarter -> Month ->PE (period ending) ->Date
The rollups only working at Year, Quarter, Month and PE level at the moment,

But I want to do a dynamic (or more flexible) YTD calculation which sum up all the level 0 elements to today's date.

In my Continuous Time Dimension Element Attributes, I have all sorts of different attributes, or please let me know if I need to create extra attributes. Please see attached screenshot of my Continuous Time Dimension Element Attributes.

Please kindly provide assist with this help.

Thank you
W

[EvgenyT]

Hello Fellow Ozzie,

Create YTD consol for each day e.g 20200120 YTD with a TI process.
You could have a separate Reporting Cube with Period YTD dimension (just as an example).
Rule will be too inefficient, you will probably end up blowing the feeder stack too with deep recursion

Another option is to have a YTD measure and let the source system do the heavy lifting if it's possible. Then you just simply load YTD balance fo the day. If it's not possible to do in the source system then you can calculate YTD balance for each day via TI. Should be relatively straightforward with no locking, so you could run it intraday.

My 2c

Thanks

Evgeny

[lotsaram]

You don't need extra attributes. What you need is dimension structure. That is you need to add rollups for YTD elements. By far the fastest and most efficient way to do additive calculations in TM1 is via dimension rollups.

YTD rollups can be created quite easily via TI process. There are a number of different options for how to structure such rollups. In Planning Analytics you do need to be aware of the potential memory impact of larger dimensions which have elements with many parents. Discussed here.

[PavoGa]

If I understand, you need to be able to calculate the total hours between two given dates. Typically, your first date is anchored to the beginning of the fiscal year.

Here is a solution that allows the calculation between any two dates along a continuous timeline. Using a TI, populate each day's hours (8?) and then also calculate an index number of those hours. So:

Date	Hours	IndexVal
01/01/2020, Wed	0	0
02/01/2020, Thu	8	8
03/01/2020, Fri	8	16
04/01/2020, Sat	0	16
05/01/2020, Sun	0	16
06/01/2020, Mon	8	24

To calculate the hours between any two given dates:

Code: Select all

nFirstDayIndex = ATTRN(dimDate, sFirstDate, 'IndexVal');
nLastDayIndex = ATTRN(dimDate, sLastDate, 'IndexVal');
nFirstDayHours = ATTRN(dimDate, sFirstDate, 'Hours');

nNetHours = nLastDayIndex - nFirstDayIndex + nFirstDayHours;

[EvgenyT]

If I understand, you need to be able to calculate the total hours between two given dates. Typically, your first date is anchored to the beginning of the fiscal year.

Here is a solution that allows the calculation between any two dates along a continuous timeline. Using a TI, populate each day's hours (8?) and then also calculate an index number of those hours. So:

Date	Hours	IndexVal
01/01/2020, Wed	0	0
02/01/2020, Thu	8	8
03/01/2020, Fri	8	16
04/01/2020, Sat	0	16
05/01/2020, Sun	0	16
06/01/2020, Mon	8	24

To calculate the hours between any two given dates:

Code: Select all

nFirstDayIndex = ATTRN(dimDate, sFirstDate, 'IndexVal');
nLastDayIndex = ATTRN(dimDate, sLastDate, 'IndexVal');
nFirstDayHours = ATTRN(dimDate, sFirstDate, 'Hours');

nNetHours = nLastDayIndex - nFirstDayIndex + nFirstDayHours;

I think he wants YTD value for each day, i.e. what I and Lotsaram suggested above

I want to do a dynamic (or more flexible) YTD calculation which sum up all the level 0 elements to today's date

[paulsimon]

Hi

As Lotsaram says, consolidation is the way to do this. Rules are in general 100 times slower than consolidation. However, be careful how you arrange the rollups. In the past I would have done this using a chaining method like this.

1st Jan YTD = 1st Jan

2nd Jan YTD = 1st Jan YTD + 2nd Jan

3rd Jan YTD = 2nd Jan YTD + 3rd Jan

This worked fine before Planning Analytics. However, in Planning Analytics this sort of approach causes an explosion in the memory taken up by MUN values.

It seems that you only need a YTD calculation, not a CTD calculation from the beginning of time to date?

If so, consider splitting the cube to have year and day dimensions

If that is not practical, then consider defining 12 consols for each month in each year. Then define YTD for the months. Do this by defining 12 separate consolidations of the month consolidations. Do not use the chaining method. The MUN style approach used by MDX that is now the cornerstone of TM1 is a lot happier with the same low number of levels in each leg of the hierarchy.

Once you have the YTD for the Months in place, then lever this to add another layer for the days, Again, don't use the chaining method. Instead define up to 31 consolidations for all the days in the month on top of the YTD consolidation for the previous month.

I haven't actually tried this but from my understanding of how things work in PA, this should give a reasonable balance of performance with a manageable increase in memory usage.

Regards

Paul Simon

[EvgenyT]

If you dont want to mock around with multiple consolidations and balance usability vs performance (MUN issue), then
can just increment YTD Balance Measure for each day via TI: closing balance for the prior day + new value

Thanks

Evgeny

[Wim Gielis]

The question is whether you need at any point in time just 1 cumulative total (for example today YTD) or whether you would need all daily YTD values for 1 year for instance. In the former case, just use a TI to break the dimension structures and rebuild. You could use other dimension totals for months to be part of your sum, for months in the YTD that have been finalized.

[WayneBo]

Create YTD consol for each day e.g 20200120 YTD with a TI process.
You could have a separate Reporting Cube with Period YTD dimension (just as an example).
Rule will be too inefficient, you will probably end up blowing the feeder stack too with deep recursion

Thanks all, Hi EvgenyT, as I am quite new to TM1, could you please kindly provide further instruction how to do as per your comments?

As I am using TM1 Perspectives from the RDS IBM provided.

Please see the screenshot of my Continuous Time dimension, as you can see, I have calendar year layout in this time dimension.
But, the financial year in Australia is from 1st July - 30th June, so ,if I do a financial YTD is 1st July 2019 to today's date ( today's date is whatever the date that currently day)
]
Thank you so much.
W

[Wim Gielis]

As asked before, do you need 1 YTD (for today or yesterday for example), or each day's YTD ?

[WayneBo]

As asked before, do you need 1 YTD (for today or yesterday for example), or each day's YTD ?

Hi Wim, thanks for your reply.

I suppose I just need 1 YTD, so eg, if user open an report today, the YTD will adding up from 1st July 2019 to 22 Jan 2020, and if user open the report tomorrow, then, the YTD will auto calculate adding up from 1st July 2019 to 23 Jan 2020.

Thanks in advance.
W

[Wim Gielis]

Then create a TI process that creates the YTD. You can do so by looping over dates.

You can 'unwind' that YTD total entirely (taking away all children), and add them again 1 after the other in your loop.
Or, if it's just adding 1 day at a time, you can do the DimensionElementComponentAdd of just that 1 day.
Method 1 is safer, though.

The TI process could be called from a button and/or used in a chore that runs just after midnight.

Also, like I already wrote, you can have the monthly total of fully elapsed months in the YTD. The sum total for YTD will of course be equal but the difference is if you are going to use the YTD element to allow users to drilldown. Do they want to see 1, 2, 3, ..., 365 elements, or will they expand and see elapsed months in total (where again they can drill into to see the day values).

[EvgenyT]

Wim, if he simply wants a YTD balance for the day then why even bother with consolidations... YTD Balance measure which is incremented daily?

Cheers

Evgeny

[Wim Gielis]

That's an option too. You can store the values against the measure and both daily value and YTD value are visible. Added benefit: you can see the YTD values of past days too.

One point of concern might be, changing values for the past in retrospect. Then consolidations do not need to be reprocessed while numbers stored against measures, would need to be calculated again.

[EvgenyT]

@ Wim Yes, you may need some extra logic to process data in retrospective.

@WayneBo, I think you have a number of options now that you can work with

Thanks

Evgeny

[WayneBo]

Thank you very much everyone.

I will have look and trying it.

Very much appreciated all your supports.

W