TM1 Forum

Posted: **Mon May 18, 2009 8:44 pm**

Hello everybody,

I want to calculate YTD and I wrote this rules ...

SKIPCHECK;

['YTD N' ] =
	if(NUMBR(ATTRS('4 Time Month', !4 Time Month, 'Month Num'))=1 % (ATTRS('4 Time Month', !4 Time Month, 'Month Num')@=''),
									DB('A Sales Analysis', !2 Company, !5 Version, 'MONTHLY N', !4 Time Month, !2 Application, !2 Bill To Country, !2 Territory BU, !2 BU, !2 Customer, !2 Products All, !1 A SA Measure),
									DB('A Sales Analysis', !2 Company, !5 Version, 'MONTHLY N', !4 Time Month, !2 Application, !2 Bill To Country, !2 Territory BU, !2 BU, !2 Customer, !2 Products All, !1 A SA Measure)
								 +DB('A Sales Analysis', !2 Company, !5 Version, 'YTD N', DIMNM('4 Time Month',DIMIX('4 Time Month',!4 Time Month)-1), !2 Application, !2 Bill To Country, !2 Territory BU, !2 BU, !2 Customer, !2 Products All, !1 A SA Measure));


FEEDERS;

['MONTHLY N']=>['YTD N'];

That works fine but I'm not sure that the correct syntax.
It's a very big cube and that takes 30 seconds to refresh datas

If I put N:.... I don't have the good result....

What do you think about this rule ?

Thx...

Posted: **Tue May 19, 2009 12:56 am**

Hi,

Maybe try some consolidations in your month dimension.
Something like:

C YTD Feb
OBal
Jan
Feb

Do a search, there are lots of posts on YTD calculations.

Cheers

Posted: **Tue May 19, 2009 6:29 am**

I think Gregor has pointed you in the right direction but from the looks of it what you are trying to do is both highly unusual and highly inefficient in a TM1 model. Consolidations work best for YTD calculations, and best of all they don't require rules or feeding.

If you really need a "YTD" node that is always the current year to date without the user picking the appropriate month node this could be achieved by having an N level "YTD" element in the month dimension and holding a string value in a parameters cube for the current month (or last month of actuals). Assuming consolidations are set up for JAN YTD, FEB YTD, MAR YTD, ... etc. It would look something like this.

Code: Select all

['YTD'] = N: DB( 'Sales', !Version, !Company, !Product, !Year, DB('System Parameters', 'Current Month', 'String') | 'YTD', !Sales Measure);

FEEDERS;
['All Months'] => ['YTD'];

TM1 Forum

How to feed correctly this rules

How to feed correctly this rules

Re: How to feed correctly this rules

Re: How to feed correctly this rules