TM1s.cfg parameter to have Excel serial dates

[Wim Gielis]

When we want to enable the use of Microsoft Excel serial dates instead of Planning Analytics serial dates, we would use UseExcelSerialDate=T in the TM1s.cfg file: https://www.ibm.com/docs/en/planning-an ... serialdate

However, this has implications, for instance on determining the day of the week (1 to 7, e.g.)

The typical way of calculating (also in Bedrock) is:

Code: Select all

        nDayIndex = Mod( DayNo ( sDate ) + 21915, 7 );
        If( nDayIndex = 0 );
            sWeekday = 'SUN';
        ElseIf( nDayIndex = 1 );
            sWeekday = 'MON';
        ElseIf( nDayIndex = 2 );
            sWeekday = 'TUE';
        ElseIf( nDayIndex = 3 );
            sWeekday = 'WED';
        ElseIf( nDayIndex = 4 );
            sWeekday = 'THU';
        ElseIf( nDayIndex = 5 );
            sWeekday = 'FRI';
        ElseIf( nDayIndex = 6 );
            sWeekday = 'SAT';
        EndIf;

The 21915 offset is not easy to remember. It should just be a multiple of 7, then -2. So why not using 5 instead of 21915 ? Anyway we digress

The above works fine for UseExcelSerialDate=F.

When UseExcelSerialDate=T, the logic breaks.

For instance today, 11 September 2023, is a Monday.
UseExcelSerialDate=F gives us 'MON', UseExcelSerialDate=T gives us 'SUN'.

Below I suggest 3 different ways to calculate the weekday (MON = 1, SUN = 7). All 3 are almost oneliners:

Code: Select all

sDate = '2023-09-11';


########################################################
# OPTION 1: the FormatDate function
########################################################
# Result: 1 = Sunday, ..., 7 = Saturday
nWeekday = Numbr( FormatDate( DayNo( sDate ), 'e', 0 ));

# Rework to: 1 = Monday, ..., 7 = Sunday
nWeekday = If( nWeekday = 1, 7, nWeekday - 1 );

AsciiOutput( 'test.txt', 'OPTION 1: ' | NumberToString( nWeekday ));


########################################################
# OPTION 2: the NumberToStringEx function
########################################################
# Result: 1 = Sunday, ..., 7 = Saturday
nWeekday = Numbr( NumberToStringEx( DayNo( sDate ) + 21916 - Dayno( '1960-01-01' ), 'w', '', '' ));

# Rework to: 1 = Monday, ..., 7 = Sunday
nWeekday = If( nWeekday = 1, 7, nWeekday - 1 );

AsciiOutput( 'test.txt', 'OPTION 2: ' | NumberToString( nWeekday ));


########################################################
# OPTION 3 the Mod function
########################################################
# Result: 0 = Sunday, ..., 6 = Saturday
nWeekday = Mod( DayNo( sDate ) + Dayno( '1960-01-01' ) / 21916 - 2, 7 );

# Rework to: 1 = Monday, ..., 7 = Sunday
nWeekday = If( nWeekday = 0, 7, nWeekday );

AsciiOutput( 'test.txt', 'OPTION 3: ' | NumberToString( nWeekday ));

sDate is an example and can be changed for testing, looking in the text file.

This should work for both values of UseExcelSerialDate. Can others test this as well ? The code was only written today and it is a new insight as far as I am aware (Bedrock will also need adjusting if I am correct).

[Wim Gielis]

For those of you who want to see a bit more coding on the 3 options, here is a preliminary version with more coding:

Code: Select all

DatasourceASCIIQuoteCharacter = '';

sDate = '2023-09-11';


########################################################
# OPTION 1: the FormatDate function
########################################################
# Result: 1 = Sunday, 7 = Saturday
nWeekday = Numbr( FormatDate( DayNo( sDate ), 'e', 0 ));

# Rework to: 1 = Monday, 7 = Sunday
If( nWeekday = 1 );
   nWeekday = 7;
Else;
   nWeekday = nWeekday - 1;
EndIf;

AsciiOutput( 'test.txt', 'OPTION 1: ' | NumberToString( nWeekday ));




If( Dayno( '1960-01-01' ) = 0 );
   # 0 ==> UseExcelSerialDate = F
   vDateSetting = 'F';
Else;
   # 21916 ==> UseExcelSerialDate = T
   vDateSetting = 'T';
EndIf;

AsciiOutput( 'test.txt', vDateSetting );


########################################################
# OPTION 2: the NumberToStringEx function
########################################################
# Result: 1 = Sunday, 7 = Saturday
If( vDateSetting @= 'F' );
   nWeekday = Numbr( NumberToStringEx( DayNo( sDate ) + 21916, 'w', '', '' ));
Else;
   nWeekday = Numbr( NumberToStringEx( DayNo( sDate ), 'w', '', '' ));
EndIf;

# Rework to: 1 = Monday, 7 = Sunday
If( nWeekday = 1 );
   nWeekday = 7;
Else;
   nWeekday = nWeekday - 1;
EndIf;

AsciiOutput( 'test.txt', 'OPTION 2: ' | NumberToString( nWeekday ));


########################################################
# OPTION 3 the Mod function
########################################################
# Result: 0 = Sunday, 6 = Saturday
If( vDateSetting @= 'F' );
   # nWeekday = Mod( DayNo( sDate ) + 21915, 7 );
   nWeekday = Mod( DayNo( sDate ) - 2, 7 );
Else;
   # nWeekday = Mod( DayNo( sDate ) + 21916, 7 );
   nWeekday = Mod( DayNo( sDate ) - 1, 7 );
EndIf;

# Rework to: 1 = Monday, 7 = Sunday
If( nWeekday = 0 );
   nWeekday = 7;
EndIf;

AsciiOutput( 'test.txt', 'OPTION 3: ' | NumberToString( nWeekday ));

[JohnO]

With UseExcelSerialDate=T in the cfg file I wrote the following back in 2018 as part of an orchestration TI.

s_Weekdays = 'SunMonTueWedThuFriSat';
n_Weekday = 0;
s_Dayname = '';

n_Weekday = Mod( DayNo( Today) -1 ,7);
s_DayName = Subst(s_Weekdays,(n_Weekday * 3)+1,3);

[Alan Kirk]

To be honest I for one never bother calculating weekdays any longer. I just have a big-@$$3d 3 dimensional lookup cube which has dates from a few years back out to 2051.

Dimension 1 is the dates, dimension 2 is the measure, dimension 3 is the measure type (string or numeric).

Measure is a whole bunch of things like calendar month, financial month, weekday (as string or number), next year, last year, next period, last period, blah, blah.

Why?

• That way it doesn't matter whether the model is using Excel dates or not. (Though I confess that we got lucky; a new ERP meant that we could dump all of our old models and start from scratch with Excel dates on).
• The dates have aliases, including serial number codes. Oooooh so many aliases. Every alias you could possibly imagine (except American ones, because of the ambiguity problem), because I got so utterly sick and freaking tired of the multitude of different date formats that our source systems provided. So now rather than doing endless string parsing and mental gymnastics I can just feed that into the calendar cube's date dimension and get a consistent format.
• The Gregorian calendar was invented to make hemorrhoids look good in comparison, especially when you need to figure out periods ahead or behind for months that don't have the same number of days much less weeks. Instead I just have all of those calculations done in advance, loaded and I just look them up as and when I need them.

The cube does use a bit over 20 meg, but that's hardly pushing the limits these days.

So what happens when we hit 2051?

I anticipate that it will be someone else's problem, assuming that IBM's marketing strategy for TM1/PA hasn't blown the product up by then. Of course, I don't rule that out. However if I'm still doing this kind of job in 2051, shoot me. Seriously, please.

However I'll make sure that I update my Dates And Times post before then. I'm aware that it needs it on several fronts. It's on my "To Do" list along with about 3,000 other things.