TM1 Forum

hi,

have a timescale 1d month and years combined, goes out many years. would like to use a YTD figure to calculate a measure in a cube rule. I know I can add equivalent number of YTD elements as there are months and then create and maintain a YTD rollup for each month for every year.

But would like an alternate to prevent maintenance and clutter with YTD alternate hierarchy. So, has anyone had experience with achieving a YTD value in a rule for calculation for a single 1d month/year timescale? YTD will be calculated for all months based on there position in the financial year. I am looking at having months numbered attribute for timescale and then referencing these as a sort of index...

haven't worked out the method completely yet so would be great to get some further tips before moving to far down this track...

cheers in advance...

How about restructuring the cube to have year in a separate dimension from months / days?

Or have both.
When doing time based modelling I take data from a 2d time cube to a 1d time cube and summarise the results back to 2d.
Need to make sure you have enough ram, but it's quite feasible.

BigG,

I think yout best bet is consolidations in your 1d dimension, it should be pretty straightforward to get the maintenance done via TI and if your users are really annoyed by the extra structures then you can use security to hide them. In my experience, with a large 1d time dimension users tend to be using subsets anyway so it's only really the admins that end up seeing the clutter.

Cheers,

Hi BigG,

Certainly you can achieve calculating YTD using rules. You would simply have a separate dimension (or you can also use your measure dimension) that has "Period Value" and "YTD Value" elements. If you want to achieve this via feeder-less rules, you can make "YTD Value" a parent of "Period Value".

You should also be careful about what your rules does on aggregate levels of your 1-d time dimension. You may have some specific business requirements around this. (For example, generally YTD value of a full year should display the one in last month.).

Although having a YTD aggregation is such a typical common business requirement in almost all planning or reporting applications, unfortunately you have to code your custom rules in TM1 to achieve this. This reminded me how life was easier for such requirements when delivering the similar requirements with Cognos Planning, where YTD BiF would be the simple answer to your question. YTD Value = YTD(Period Value)

Regards

thanks all for your comments. Very helpful

We generally have 2 d timescales but for this particular cube the years are many but only the 1st few years are broken into months, while the rest are annual entry. Due to this we chose a single d. Sorry I didn't expand on this earlier but thought it may confuse.. or not.

Therefore the YTD is only required for Years with months.

I think I will try a couple of options - seeing the effect of adding ytd consolidated elements in timescale dim (deal with subsets) and/or trying the rules calculated 'ytd value' in measures dim as parent of 'period value' .

Any further tips and comments would be great. cheers in advance