Year-to-date values in 2 dimensions
- Jul. 10, 2015
Introduction
A request that is very usual in TM1 is to have the system (that is, the cubes) calculate Year-to-date values.
In general we have 2 solutions, each situation might require a different approach:
- Set up consolidations in your time dimension, where the relevant elements roll up into new consolidated elements (very fast approach)
- Use a second dimension with elements like 'Monthly' and 'Year-to-date', where TM1 rules or Turbo Integrator will calculate the Year-to-date element
Alternative approach
Today I implemented a different method to calculate the Year-to-date values for weeks. As the customer has a time dimension containing days and weeks for a history of more than 10 years, adding consolidated elements as in solution 1 will be possible but the dimension will be very long and not very easy to use. Approach 2 would be possible but rules would be a performance hit and Turbo Integrator would not be ideal here too. We would love to have our performance at the same level, so having consolidations is the only practical way to proceed.
I decided to use an extra dimension, in the screenshot above that dimension is in the columns. You will understand that adding year-to-date consolidations for weeks in the dimension Cmn_DayWeek will not be the nicest solution (though it is perfectly feasible). By adding the dimension Cmn_Week we only have to set up the weekly year-to-date consolidations once (in that dimension compared to the dimension Cmn_DayWeek). Below I show an extract of that dimension:
Looking back at the first screenshot you now see what happens. By loading the data in the cube at the daily level (dimension Cmn_DayWeek) ánd at the weekly level (dimension Cmn_Week) we can find the Year-to-date values in the red rectangle. For illustration purposes I entered a value of 1 in all relevant cells but the principle of calculating year-to-date values remains the same with any other value (or some days without values).
You can apply the same technique to year-to-date values in a Period or Month dimension. The 'other' additional dimension would then contain 12 monthly elements and 12 year-to-date consolidated elements.
[For your information, the fiscal year for above customer case starts in February, so I took Week 5 as the first week of the year. After week 53 we still have week 1, week 2, week 3, week 4. After week 4 we reach 365 or 366 as the year-to-date value.]
Enjoy your (hopefully sunny) weekend !
