How to create rollup YTD measures in TM1 using rule

Check list of all posts

Context

First of all, we can get YTD rollup in report or in cube. Building YTD in report is not most efficient way, as we cannot share this logic in model, and it requires MDX to get YTD value. Therefore, the best way is to build it in cube side, as it is easy to build and can be shared in many cases, such as report, cube view, worksheet, etc.

A common way is to create the YTD periods as rollups in the month dimension. You add roll-ups like YTD Feb (consisting of Jan and Feb), YTD Mar (consisting of Jan to Mar or consisting of YTD Feb and Mar).   Etc. then the YTD values will be automatically calculated from the data on the Jan to Dec elements. This solution has two disadvantages: a) it touches month dimension, which could have impact on everywhere; and b) it is not very flexible to be used in report. The best way is to create a YTD measure. This document will demonstrate this idea in detail.

Sample

Month dimension (Fiscal month)

Measure dimension

Cube

Rule

['YTD Amount']=N: DB('Sales Cube',!Customers,

ATTRS('Month',!Month,'Previous_Month'),'YTD Amount') + ['Amount'];

['Amount']=>DB('Sales Cube',!Customers,

ATTRS('Month',!Month,'Next_Month'),'YTD Amount');

Result

Note

The TM1 rules engine is extremely flexible and allows rules to be written that are “recursive” in nature. This functionality opens a door to resolve some complicated calculations.

As YTD is usually used when the basic measures is already calculated, we need to make sure that all other rules should proceed first, but leave YTD excluded. Therefore, introducing YTD may change all existing rules.