Saturday, March 1, 2014

How to create rollup YTD measures in TM1 using rule

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.

1 comment:

  1. I tried this and got the formula to work, except that the YTD Amount in the first month of my fiscal year is zero. Did I miss a step? I have no other rules on this cube.

    FEEDSTRINGS;
    SKIPCHECK;

    ['YTD Amount']=N:DB('GL Reporting',!h_entity,!v_scenario,
    ATTRS('t_month',!t_month, 'Previous_Month'),!h_gl_account,!h_department,'YTD Amount')+['Amount'];


    FEEDERS;

    ['Amount']=>DB('GL Reporting',!h_entity,!v_scenario,
    ATTRS('t_month', !t_month, 'Next_Month'),!h_gl_account,!h_department,'YTD Amount');

    ReplyDelete