Day of Week in TI

[nickmas]

I search a function to find the day of the week in a TM1 process.
So, for example: 1 = Sunday, 2 = Monday, ..., 6 = Saturday.

I now the format for day of the week is \w but in wich function do I have to use this format?
Because in the TIMEST() function, dus format is not supported...

Can anyone help me please? Thanks!

{Admin Note: This was moved from the Useful Code, Tips and Tricks forum. The answer may be useful code. The question isn't.}

[Alan Kirk]

I search a function to find the day of the week in a TM1 process.
So, for example: 1 = Sunday, 2 = Monday, ..., 6 = Saturday.

I now the format for day of the week is \w but in wich function do I have to use this format?
Because in the TIMEST() function, dus format is not supported...

Can anyone help me please? Thanks!

First you need to get the date into a DateSerial format. The Today() function will give you the serial date of the current day. Otherwise, use the DayNo function.

You can then use the following, though it returns Sunday as 0:

# 0 = Sunday, 1 = Monday to 6 = Saturday.
l_DayOfWeek= Mod ( DayNo( TODAY ) + 21915, 7);

[nickmas]

Ok, thank you very much!!

But why + 21915?

[Alan Kirk]

Ok, thank you very much!!

But why + 21915?

It's the difference in serial dates between TM1's system (which starts on 1/1/1960) and Excel's (which starts on 1/1/1900). It would be possible to do a similar calculation using TM1's date system alone but there's sometimes a need to do conversions to and from an Excel date serial number so I just find it easier to leave that in.

[nickmas]

Ok, thanks!

So I can just use that code in my TM1 process and it will work?
(I can not try it now, because I am not @ the office)

[Alan Kirk]

Ok, thanks!

So I can just use that code in my TM1 process and it will work?
(I can not try it now, because I am not @ the office)

Yes; I just have it in my code library as a "copy and paste" block. But as I said, this one renders Sunday as 0, not 1. You can modify it accordingly. (Just add 1 to it.)

[Wim Gielis]

Resurrecting from the deads but with good information:

Code: Select all

# weekday '1' = Sunday, '7' = Saturday
sWeekday = NumberToStringEx( Now + 21916, 'w', '', '' );

# 1 = Sunday, 7 = Saturday
iWeekday = Numbr( NumberToStringEx( Now + 21916, 'w', '', '' ));

Code: Select all

# weeknumber 1 to 54
sWeeknumber = NumberToStringEx( Now + 21916, 'ww', '', '' );

Code: Select all

# quarter '01' to '04'
sWeeknumber = NumberToStringEx( Now + 21916, 'q', '', '' );

[Alan Kirk]

I'm aware that I need to update ye olde Using Dates And Times thread with that as well, but I need to update it with a number of things. Right at the moment I'm testing out issues relating to the new Excel Dates server config parameter and plan to whack the whole thing with a complete overhaul.

[Wim Gielis]

That would be good, thanks !

Too bad that this:

Code: Select all

NumberToStringEx( Now + 21916, 'C', '', '' );

(mind the C) gives colons in the output. For me, currently:

Sat Jun 29 00:49:04 2019

If not I could use it in temporary views and subsets, as a replacement for a long Timst statement.

[Bakkone]

Right at the moment I'm testing out issues relating to the new Excel Dates server config parameter and plan to whack the whole thing with a complete overhaul.

I got stuck on trying to figure out the location of the tm1s.cfg. Also how general thing should be to work with both windows and linux machines. Then I had too many variables on my list and gave up.

[Wim Gielis]

I got stuck on trying to figure out the location of the tm1s.cfg.

Are you an admin person of that TM1 model, or what is your role with respect to TM1 ?

[Bakkone]

Are you an admin person of that TM1 model, or what is your role with respect to TM1 ?

Yes I'm an admin/developer. I was working on some sort of "ultimate period/time dimension creator". But to calculate some things I needed to know where the tm1s.cfg file was located. Since I need to know if the model used tm1 dates or excel dates.

The only real practical solution I could think of was to store it in a control cube somewhere. But at that point I was on to new adventures. If I had more time I would try to find the answer using the REST API. It seems its a magic answer to most things. If so i could just create a .cmd script that in theory would work on both windows and *nix machines.

Now that I think about it I would probably end up storing the result in a cube somewhere anyway. So maybe just build the thing and worry about automatically populating the cube later.

[Emixam]

Hi,

Take a look at this link for tm1s.cfg :

https://www.ibm.com/support/knowledgece ... 5038e.html

And this one for more explanation on the UseExcelSerialDate parameter:

https://www.ibm.com/support/knowledgece ... ldate.html


Cheers !

[Bakkone]

And this one for more explanation on the UseExcelSerialDate parameter:
https://www.ibm.com/support/knowledgece ... ldate.html

It never even occured to me to derive the system used by checking the output. Now you made me feel like a complete idiot

Thanks a bunch!

Will try to find some time to rebuild my dim.period process.