How to feed correctly this rules

[pobrouwers]

Hello everybody,

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

Code: Select all

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...

[Gregor Koch]

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

[lotsaram]

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'];