Getting the last date of a month in TI
-
- Posts: 5
- Joined: Fri Nov 11, 2011 4:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: Excel 2012
Getting the last date of a month in TI
Hi,
I'm used to get the last day of a month as the day before the first day of the next month:
DATES ( myYear, myMonth + 1, 0)
This works in Excel well but not in Tm1 TI Process!
Month have to be there between 1 and 12 and days have to be between 1 and 28, 29, 30 or 31. Everything else lead to process break. And yes, I have to manage leap-year on my own.
Is there any workaround?
Background: Invoice have to be paid on the last day of the next month, over-next month and so on... but which is the last day?
Thanks for your expertice.
Regards Benoit
I'm used to get the last day of a month as the day before the first day of the next month:
DATES ( myYear, myMonth + 1, 0)
This works in Excel well but not in Tm1 TI Process!
Month have to be there between 1 and 12 and days have to be between 1 and 28, 29, 30 or 31. Everything else lead to process break. And yes, I have to manage leap-year on my own.
Is there any workaround?
Background: Invoice have to be paid on the last day of the next month, over-next month and so on... but which is the last day?
Thanks for your expertice.
Regards Benoit
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Using Dates And Times In TM1
For what's worth, I use a calendar dimension with all the attributes set for a particular day. The day dimension is integers with American and European data style aliases. Mine runs back to the 1920s. For information I cannot easily get from a TI or rule function, it is an attribute. All kinds of things like the fiscal period, fiscal year, remaining workdays in the period, DOW, Holidays, etc. It is nice and very useful and prevents code to figure certain things out.
And a side about the discussions on fiscal years not coinciding with the calendar year. I had one client who designated their fiscal year as the year in which the first month of the fiscal year is in. So a May, '16 through April '17 fiscal year was designated as Fiscal Year '16. Been a controller, accountant and have traded stocks since I was 18 and can honestly say I had never seen or noticed that kind of nomenclature until then. At least here in the States.
And a side about the discussions on fiscal years not coinciding with the calendar year. I had one client who designated their fiscal year as the year in which the first month of the fiscal year is in. So a May, '16 through April '17 fiscal year was designated as Fiscal Year '16. Been a controller, accountant and have traded stocks since I was 18 and can honestly say I had never seen or noticed that kind of nomenclature until then. At least here in the States.
Ty
Cleveland, TN
Cleveland, TN
-
- MVP
- Posts: 3123
- 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: Getting the last date of a month in TI
Hello Benoit,
Can you retrieve the first day of the new month in serial number format, subtract 1 and then manipulate the result to get the desired formatted outcome ?
Can you retrieve the first day of the new month in serial number format, subtract 1 and then manipulate the result to get the desired formatted outcome ?
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: 5
- Joined: Fri Nov 11, 2011 4:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: Excel 2012
Re: Getting the last date of a month in TI
Hello Wim and PavoGa,
many thanks for your inspiring suggestion.
Yes I use some Kind of serialisation. Here the workaround (all in TI, not in rules):
intM2_brutto = intSourceMonth + intNumberOfMonthToShift;
intM2_brutto = intM2_brutto + 1;
intM2 = MOD ( 599 + intM2_brutto, 12) + 1 ;
intY2 = intSourceYear + INT ( intM2_brutto / 12);
strGoalDate = DATE (DAYNO (DATES( intY2, intM2, 1)) - 1, 1);
599 is 50 * 12 months minus 1
+600 because NumberOfMonthToShift can be negativ but intM2_brutto have to be positiv
and minus 1 because you have to be between 0 and 11 to MOD over 12. Therefore -1 within MOD and +1 outside.
I was just wondering if there is any other known smarter solution.
Cheers
Benoit
many thanks for your inspiring suggestion.
Yes I use some Kind of serialisation. Here the workaround (all in TI, not in rules):
intM2_brutto = intSourceMonth + intNumberOfMonthToShift;
intM2_brutto = intM2_brutto + 1;
intM2 = MOD ( 599 + intM2_brutto, 12) + 1 ;
intY2 = intSourceYear + INT ( intM2_brutto / 12);
strGoalDate = DATE (DAYNO (DATES( intY2, intM2, 1)) - 1, 1);
599 is 50 * 12 months minus 1
+600 because NumberOfMonthToShift can be negativ but intM2_brutto have to be positiv
and minus 1 because you have to be between 0 and 11 to MOD over 12. Therefore -1 within MOD and +1 outside.
I was just wondering if there is any other known smarter solution.
Cheers
Benoit
- PavoGa
- MVP
- Posts: 618
- Joined: Thu Apr 18, 2013 6:59 pm
- OLAP Product: TM1
- Version: 10.2.2 FP7, PA2.0.9.1
- Excel Version: 2013 PAW
- Location: Charleston, Tennessee
Re: Getting the last date of a month in TI
This a good example for a calendar dimension instead having to do all this on the fly in a TI and the possibility of repeating it anytime you need something similar. Store the FirstDayOfFiscalPeriod, LastDayOfFiscalPeriod, and/or LastDayOfPreviousFiscalPeriod as attributes. Then your code is reduced to this:Benoit wrote: ↑Fri Oct 05, 2018 10:20 am Hello Wim and PavoGa,
many thanks for your inspiring suggestion.
Yes I use some Kind of serialisation. Here the workaround (all in TI, not in rules):
intM2_brutto = intSourceMonth + intNumberOfMonthToShift;
intM2_brutto = intM2_brutto + 1;
intM2 = MOD ( 599 + intM2_brutto, 12) + 1 ;
intY2 = intSourceYear + INT ( intM2_brutto / 12);
strGoalDate = DATE (DAYNO (DATES( intY2, intM2, 1)) - 1, 1);
599 is 50 * 12 months minus 1
+600 because NumberOfMonthToShift can be negativ but intM2_brutto have to be positiv
and minus 1 because you have to be between 0 and 11 to MOD over 12. Therefore -1 within MOD and +1 outside.
I was just wondering if there is any other known smarter solution.
Cheers
Benoit
Code: Select all
sLastDayOfPreviousPeriod = ATTRS(dimCalendar, sDate, 'LastDayOfPreviousFiscalPeriod');
Ty
Cleveland, TN
Cleveland, TN
-
- Posts: 20
- Joined: Wed Feb 23, 2011 6:43 pm
- OLAP Product: TM1
- Version: Latest
- Excel Version: 2013
Re: Getting the last date of a month in TI
Code: Select all
nSerialDate = ParseDate( sYear | '-' | sMonth | '-01', 'yyyy-M-dd' );
nSerialDate = nSerialDate - 1;
sLastDayPriorMonth = FormatDate( nSerialDate, 'yyyy-MM-dd' );
-
- MVP
- Posts: 3123
- 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: Getting the last date of a month in TI
To me the easiest solution, with regular TM1 date functions, are as follows.
The last day of the current month:
If you use a variable for Month( sDate ), then the code is really short. As a oneliner:
The last day of the prior month:
As a oneliner:
The last day of the current month:
Code: Select all
# 2018-10-08
sDate = Today(1);
# 21488 (= 43404 - 21916)
nLastOfTheMonth = DayNo( Dates( Year( sDate ) + If( Month( sDate ) = 12, 1, 0 ), If( Month( sDate ) = 12, 1, Month( sDate ) + 1 ), 1)) - 1;
# 2018-10-31
sLastOfTheMonth = Timst( nLastOfTheMonth, '\Y-\m-\d' );
Code: Select all
sDate = Today(1);
sLastOfTheMonth = Timst( DayNo( Dates( Year( sDate ) + If( Month( sDate ) = 12, 1, 0 ), If( Month( sDate ) = 12, 1, Month( sDate ) + 1 ), 1)) - 1, '\Y-\m-\d' );
Code: Select all
sDate = Today(1);
nLastOfTheMonth = DayNo( Dates( Year( sDate ), Month( sDate ), 1 ) - 1;
sLastOfTheMonth = Timst( nLastOfTheMonth, '\Y-\m-\d' );
Code: Select all
sDate = Today(1);
sLastOfTheMonth = Timst( DayNo( Dates( Year( sDate ), Month( sDate ), 1 ) - 1, '\Y-\m-\d' );
Last edited by Wim Gielis on Mon Oct 08, 2018 8:05 pm, edited 2 times in total.
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
-
- MVP
- Posts: 3123
- 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: Getting the last date of a month in TI
Good idea to use FormatDate and ParseDate ! I'm a fan of these.
ParseDate and FormatDate can be really useful if the pattern of the dates can change. We do not necessarily rely upon yyyy-mm-dd and we are much more free in terms of input and output formats. For the last day of the prior month, we subtract the day number from the date:
This becomes the following code if we choose our input and output format:
Do you require the output to be 'Sunday, 30 September 2018' instead of '2018-09-30' ? No problem at all:
Even better, if the input is done in some local variant and you need to interpret those 'text dates' to convert to a different format. Or the input is done in a different language. See the below sources if you want to experiment.
Sources:
http://www.wimgielis.com/tm1_newdateformatter_EN.htm
http://userguide.icu-project.org/formatparse/datetime
ParseDate and FormatDate can be really useful if the pattern of the dates can change. We do not necessarily rely upon yyyy-mm-dd and we are much more free in terms of input and output formats. For the last day of the prior month, we subtract the day number from the date:
Code: Select all
# 2018-10-08
sDate = Today(1);
nSerialDate = ParseDate( sDate, 'y-MM-dd' ) - Numbr( FormatDate( ParseDate( sDate, 'y-MM-dd' ), 'd' ));
sLastDayPriorMonth = FormatDate( nSerialDate, 'y-MM-dd' );
Code: Select all
cDateFormat_Input = 'y-MM-dd';
cDateFormat_Output = 'y-MM-dd';
nSerialDate = ParseDate( sDate, cDateFormat_Input ) - Numbr( FormatDate( ParseDate( sDate, cDateFormat_Input ), 'd' ));
sLastDayPriorMonth = FormatDate( nSerialDate, cDateFormat_Output );
Code: Select all
cDateFormat_Output = 'EEEE, dd LLLL y';
Sources:
http://www.wimgielis.com/tm1_newdateformatter_EN.htm
http://userguide.icu-project.org/formatparse/datetime
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: 20
- Joined: Wed Feb 23, 2011 6:43 pm
- OLAP Product: TM1
- Version: Latest
- Excel Version: 2013
Re: Getting the last date of a month in TI
Yes, I find them much quicker and easier to use than the regular TM1 time/date functions.Wim Gielis wrote: ↑Mon Oct 08, 2018 8:05 pm Good idea to use FormatDate and ParseDate ! I'm a fan of these.
-
- Posts: 5
- Joined: Fri Nov 11, 2011 4:38 pm
- OLAP Product: Tm1
- Version: 10.2.2
- Excel Version: Excel 2012
Re: Getting the last date of a month in TI
@ndivine: wow!
It's simple & it's understandable while reading --> nice!
Thanks to all contributers for the suggestions.
Best regards
Benoit
It's simple & it's understandable while reading --> nice!
Thanks to all contributers for the suggestions.
Best regards
Benoit
-
- Community Contributor
- Posts: 292
- Joined: Tue Sep 06, 2016 7:55 am
- OLAP Product: TM1
- Version: 10.1.1
- Excel Version: Excel 2010
Re: Getting the last date of a month in TI
Yeah I like FormatDate and ParseDate, just wish I had known about them before now!
Are there any other functions like these that are lurking in a darkened corner or have I missed something in the documentation?
Are there any other functions like these that are lurking in a darkened corner or have I missed something in the documentation?