TM1 has _some_ date arithmetic

Post Reply
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

TM1 has _some_ date arithmetic

Post by David Usherwood »

Did some digging for a client, thought it was worth sharing/peer reviewing.

The user guide describes date formats available in TM1. Besides the usual stuff there are some less common ones:

ddd Day of the week eg Tue
w Numeric day of the week - Tuesday would be 3
ww Week number of the year
q Quarter

All these work off datetime values (numbers with the date on the left of the decimal point and times on the right). Oddly, although the rules engine works on a 'Day 1' of 1/1/1960 as opposed to Excel's Day 1 of 1/1/1900, the formats assume Excel's Day 1 - so add 21916 first.

You can use these in views or in element formats. It wasn't immediately obvious how to process them further, but it turns out you can. The (new-ish) TI command NumbertoStringEx is written up as relating to numeric formats, but actually works with date formats as well. You need to supply decimal and thousands separators but they aren't used.

So:
numbertostringex(value,'dddddd-ddd-w-ww','.',',') where value is the _Excel_ number for 1/1/2000 (36526) gives you

Saturday, January 01, 2000-Sat-7-1

Where 7 is the day of the week and 1 is the quarter.

NB The docs say one of the formats - y - gives you the day of the year. It doesn't work.
ASEVEREYNS
Posts: 4
Joined: Mon Jun 02, 2008 3:28 pm

Re: TM1 has _some_ date arithmetic

Post by ASEVEREYNS »

In addition to that, this is an easy way to determine the weekday of today in TI:

Code: Select all

vtoday=NumberToStringEx(DayNo(TODAY)+21916,'ddd','','');
Which givs you Mon, Tue, ..., Sat or Sun as a result.
I've used this in the past to determine whether a bunch of load processes should be triggered or not (given that the source CSV files are only there on weekdays).
Post Reply