Day of Week in TI
-
- Posts: 3
- Joined: Wed Mar 17, 2010 8:54 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: Office 2003
Day of Week in TI
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.}
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
- Site Admin
- Posts: 6608
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Day of Week in TI
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.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!
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);
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 3
- Joined: Wed Mar 17, 2010 8:54 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: Office 2003
Re: Day of Week in TI
Ok, thank you very much!!
But why + 21915?
But why + 21915?
- Alan Kirk
- Site Admin
- Posts: 6608
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Day of Week in TI
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 wrote:Ok, thank you very much!!
But why + 21915?
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 3
- Joined: Wed Mar 17, 2010 8:54 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: Office 2003
Re: Day of Week in TI
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)
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
- Site Admin
- Posts: 6608
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Day of Week in TI
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.)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)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Day of Week in TI
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', '', '' );
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- Alan Kirk
- Site Admin
- Posts: 6608
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Day of Week in TI
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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Day of Week in TI
That would be good, thanks !
Too bad that this:
(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.
Too bad that this:
Code: Select all
NumberToStringEx( Now + 21916, 'C', '', '' );
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.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Day of Week in TI
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.
-
- MVP
- Posts: 3121
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Day of Week in TI
Are you an admin person of that TM1 model, or what is your role with respect to TM1 ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Day of Week in TI
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.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 ?
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.
-
- Posts: 139
- Joined: Tue May 21, 2019 3:33 pm
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 2016
- Location: The Internet
Re: Day of Week in TI
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 !
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 !
-
- Posts: 119
- Joined: Mon Oct 27, 2014 10:50 am
- OLAP Product: TM1
- Version: 10.2.2
- Excel Version: 2013
Re: Day of Week in TI
Emixam wrote: ↑Tue Jul 02, 2019 4:24 pm 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.