In case you encounter some frustration creating a TM1 rule to use the DAYNO function and it appears to not work, there is a simple reason why this is happening.

Stepping back for a moment, the TM1 user guide shows very simple syntax for this function, which works fine this way as a variable in a TI process, but does not work in a rule:

If you write the rule as such in the example below, you will not find ‘day_number’ populating with the serialized date.

[‘day_number’] = N: [‘Due_Date’];

Since TM1 stores dates (ie 13-12-20 or 2013-12-20) as STRING data, you need to use the STRING operator with the DB function.

On a side note, Excel will store the date as numeric, and that may require the Excel TEXT function to be used to turn Excel’s numeric date to TM1’s string date to load into a TM1 cube.

So, to get the above example to work, you would need your rule to look like this example:

[‘day_number’] = S: DB(‘CubeName’, !DimName1, !DimName2, ‘Due_Date’);

For more information on DAYNO function, contact us at Sales@lodestarsolutions.com.

If you liked this blog on Tricks to Working with DAYNO Function in TM1, then check out this educational piece on working with TM1 perspective websheets and active forms by clicking here.

If you have not joined the Lodestar community, what are you waiting for?  It takes less than a minute and by going to lodestarsolutions.com and signing up you will get access to all of our programs, blogs and events before those who are not in our community.  It is a sure fire way to get FREE tips and tricks for both TM1 and BI along with software egnostic items that can save you time and dollars on your next project!