TM1 Forum

Hi,

I want to calculate the movement between days. I am using the DIMIX formula and all is working fine.

What I picked up now was that for the last day in each month it takes the total for the previous month to do the calculation because for eaxmple Feb 28 dimix = 37 and Total Jan = 36 and and January 31 = 35.

My formule uses 37 and 36. I need to somehow skip 36 and use 35.
If the DIMIX -1 = consolidater, then DIMIX -2.

Any help please?
My current formula below:
['Stock']=N:
DB('Stock Plan',dimnm('Date',(dimix('Date',!Date)-1)),!Version,!Depot,!Product,'Stock')+
DB('Stock Plan',dimnm('Date',(dimix('Date',!Date)-1)),!Version,!Depot,!Product,'Delivery')-
DB('Stock Plan',dimnm('Date',(dimix('Date',!Date)-1)),!Version,!Depot,!Product,'Sales');


My thinking is something like this but it doent make sense:
['Stock']=N:
DB('Stock Plan',IF(dimnm('Date',(dimix('Date',!Date)-1)<>ELLEV(('Date',!Date)=0,dimnm('Date',(dimix('Date',!Date)-2))),!Version,!Depot,!Product,'Stock')+

I found the solution:

['Stock']=N:
DB('Stock Plan',IF(ellev('Date',dimnm('Date',(dimix('Date',!Date)-1))) <>0,dimnm('Date',(dimix('Date',!Date)-2)),dimnm('Date',(dimix('Date',!Date)-1))),!Version,!Depot,!Product,'Stock')+
DB('Stock Plan',IF(ellev('Date',dimnm('Date',(dimix('Date',!Date)-1))) <>0,dimnm('Date',(dimix('Date',!Date)-2)),dimnm('Date',(dimix('Date',!Date)-1))),!Version,!Depot,!Product,'Delivery')-
DB('Stock Plan',IF(ellev('Date',dimnm('Date',(dimix('Date',!Date)-1))) <>0,dimnm('Date',(dimix('Date',!Date)-2)),dimnm('Date',(dimix('Date',!Date)-1))),!Version,!Depot,!Product,'Sales');


Thanks

I would suggest using a prior attribute on your Date dimension, and then if possible populate it at the same time as you build the date dimension, preferably not by rules.

Then you can just keep the rules simple and fast:

['Stock']=N:
DB('Stock Plan',ATTRS('Date', !Date, 'Prior'),!Version,!Depot,!Product,'Stock')+
DB('Stock Plan',ATTRS('Date', !Date, 'Prior'),!Version,!Depot,!Product,'Delivery')-
DB('Stock Plan',ATTRS('Date', !Date, 'Prior'),!Version,!Depot,!Product,'Sales');

If you really can't get the prior day into the attributes via TI on the load, then do it with a rule in the attribute using the DAYNO function:

Assuming your date elements are in the YYYY-MM-DD format, something like:

['Prior'] = S: DATES(DAYNO(!Date) - 1,1);

Tweak as needed for you date formats etc.


If you put too many if statements in your main rules it will slow down horribly.

Also your feeder will be equally complex if you keep the if statement logic in the rule.

Another suggestion would be to do the addition and subtraction using the hierarchy and element weighting instead of a rule. It will also be much faster and simpler for the feeders.

I'd be wary of using indexes for this. As well as the problem you're facing, indexes can change if changes are made to the dimension. I would suggest using attributes or a simple lookup cube to answer questions like "yesterday's date", "last week's date", "next month's date" etc. It can take a bit of thought to get it working right but I think the benefits (easier readability and debugging) are worth it.

appreciate all the replies.

thank you