How to create rollup YTD measures in TM1 using rule – update 1

Check list of all posts

Please check last blog for detail. This document outlined three possible problems with YTD measure with TM1 rule.

Sample

1. Mostly, there is total for whole year, we want to show the total is YTD value, not the sum of YTD as below

Leaf level is applied

To show year value as total of year,

Rule should be changed as

2. Sometime, YTD works in cube view and not in report (such as using Cognos workspace advanced), some YTD values are missed when there is no sales.  Make sure that you have ['Amount']=>['YTD Amount'];

SKIPCHECK;

['YTD Amount']=DB('Sales Cube',!Customers,ATTRS('Month',!Month,'Previous_Month'),'YTD Amount') + ['Amount'];

FEEDERS;

['Amount']=>DB('Sales Cube',!Customers,ATTRS('Month',!Month,'Next_Month'),'YTD Amount');

['Amount']=>['YTD Amount'];

3. In case when all other versions are working such as budget, forecast, but actual sales may not work, as no sales are available for whole year. Your YTD data will be empty from month where no sales is available. The solution is to feed data not only for months when sales are available, but also months when sales are NOT available.

SKIPCHECK;

['Amount',{'Oct','Nov','Dec','Jan'}]=N:DB('Sale cube source',!Customers,!Month,'Amount');

['YTD Amount']=DB('Sales Cube',!Customers,ATTRS('Month',!Month,'Previous_Month'),'YTD Amount') + ['Amount'];

FEEDERS;

['Amount']=>DB('Sales Cube',!Customers,ATTRS('Month',!Month,'Next_Month'),'YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Oct','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Nov','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Dec','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Jan','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Feb','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Mar','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Apr','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'May','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Jun','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Jul','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Aug','YTD Amount');

['Amount']=>DB('Sales Cube',!Customers,'Sep','YTD Amount');