Page 1 of 1
Day of Week in TI
Posted: Wed Mar 17, 2010 8:57 pm
by 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.}
Re: Day of Week in TI
Posted: Wed Mar 17, 2010 9:08 pm
by Alan Kirk
nickmas wrote: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);
Re: Day of Week in TI
Posted: Wed Mar 17, 2010 9:25 pm
by nickmas
Ok, thank you very much!!
But why + 21915?
Re: Day of Week in TI
Posted: Wed Mar 17, 2010 9:28 pm
by Alan Kirk
nickmas wrote: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.
Re: Day of Week in TI
Posted: Wed Mar 17, 2010 9:29 pm
by 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)
Re: Day of Week in TI
Posted: Wed Mar 17, 2010 9:47 pm
by Alan Kirk
nickmas wrote: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.)
Re: Day of Week in TI
Posted: Fri Jun 28, 2019 10:40 pm
by 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', '', '' );
Re: Day of Week in TI
Posted: Fri Jun 28, 2019 10:46 pm
by 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.
Re: Day of Week in TI
Posted: Fri Jun 28, 2019 10:50 pm
by 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.
Re: Day of Week in TI
Posted: Tue Jul 02, 2019 7:53 am
by Bakkone
Alan Kirk wrote: ↑Fri Jun 28, 2019 10:46 pm
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.
Re: Day of Week in TI
Posted: Tue Jul 02, 2019 8:56 am
by Wim Gielis
Bakkone wrote: ↑Tue Jul 02, 2019 7:53 amI 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 ?
Re: Day of Week in TI
Posted: Tue Jul 02, 2019 3:09 pm
by Bakkone
Wim Gielis wrote: ↑Tue Jul 02, 2019 8:56 am
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.
Re: Day of Week in TI
Posted: Tue Jul 02, 2019 4:24 pm
by 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 !
Re: Day of Week in TI
Posted: Wed Jul 03, 2019 8:56 am
by Bakkone
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.