DayNo TM1 Function: Use and Syntax

Yesterday I was doing some work on an inventory forecasting system.  I had Year, Month and Day dimensions and needed to bring through Actuals for all days up until yesterday. I have a System Settings cube with Current Day defined in it using a rule. To bring through the Actuals I created a rule that uses the DayNo function.

Syntax of DayNo

DAYNO(‘DateString’)

So here if you insert a string in the form YY-MM-DD or YYYY-MM-DD then the rule will evaluate that to the internal TM1 number that corresponds to that day (much like Excel does, but TM1 uses a different start day to Excel – TM1 uses 1-Jan-1960 and Excel uses the start of the 20th century).

For example, if we have DayNo(‘2016-04-14’), then TM1 will return the number 20558. With this you can then add or compare other day numbers easily.

Note that the syntax of the date is absolutely vital.  It will not work with any other syntax than YY-MM-DD or YYYY-MM-DD. Thus you can’t use 02-28-2016 (MM-DD-YYYY) or 2016-Feb-28 (YYYY-MMM-DD). Importantly, you will not get an error, rather it will just not calculate.

Use of DayNo

In the model referred to above, I used it in a rule in the following way:

IF ( DayNo (  !Year | '-' | ATTRS ( 'Month',!Month,'MonthNum') | '-' | !Day )  <= DayNo ( DB ( 'System Settings', 'Current Date', 'String' ) ) , ['Actual'] , Continue );

Here I am getting the evaluated values for !Year, !Month and !Day and converting them to a DayNo, then testing that against the DayNo from the System Settings cube using a DB function. If it is true (i.e. less than today), then I am getting the Actual value and otherwise Continuing. There’s an ATTRS in there because the Month dimension uses there character months (like ‘Apr’), rather than ’04’. The attribute MonthNum has the ’04’ in it.

 

This applies to both Rules and Turbo Integrator processes.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

This entry was posted in Rule Functions, TI Functions. Bookmark the permalink.