Getting the last date of a month in TI

Post Reply
Benoit
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

Post by Benoit »

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
User avatar
PavoGa
MVP
Posts: 616
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

Post by PavoGa »

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.
Ty
Cleveland, TN
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

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 ?
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
Benoit
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

Post by Benoit »

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
User avatar
PavoGa
MVP
Posts: 616
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

Post by PavoGa »

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
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:

Code: Select all

sLastDayOfPreviousPeriod = ATTRS(dimCalendar, sDate, 'LastDayOfPreviousFiscalPeriod');
I have two TIs. One that will populate the dimension and load either all or as many of the attributes as possible. Another loads an Excel export file created because there may need to be some manipulations that are not worth doing in a TI.
Ty
Cleveland, TN
ndivine
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

Post by ndivine »

Code: Select all

    nSerialDate = ParseDate( sYear | '-' | sMonth | '-01',  'yyyy-M-dd' );
    nSerialDate = nSerialDate - 1;
    sLastDayPriorMonth = FormatDate( nSerialDate, 'yyyy-MM-dd' );
    
Wim Gielis
MVP
Posts: 3103
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

Post by Wim Gielis »

To me the easiest solution, with regular TM1 date functions, are as follows.

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' );
If you use a variable for Month( sDate ), then the code is really short. As a oneliner:

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' );
The last day of the prior month:

Code: Select all

sDate = Today(1);

nLastOfTheMonth = DayNo( Dates( Year( sDate ), Month( sDate ), 1 ) - 1;
sLastOfTheMonth = Timst( nLastOfTheMonth, '\Y-\m-\d' );
As a oneliner:

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
MVP
Posts: 3103
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

Post by Wim Gielis »

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:

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' );
This becomes the following code if we choose our input and output format:

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 );
Do you require the output to be 'Sunday, 30 September 2018' instead of '2018-09-30' ? No problem at all:

Code: Select all

cDateFormat_Output = 'EEEE, dd LLLL y';
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
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
ndivine
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

Post by ndivine »

Wim Gielis wrote: Mon Oct 08, 2018 8:05 pm Good idea to use FormatDate and ParseDate ! I'm a fan of these.
Yes, I find them much quicker and easier to use than the regular TM1 time/date functions.
Benoit
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

Post by Benoit »

@ndivine: wow!

It's simple & it's understandable while reading --> nice!

Thanks to all contributers for the suggestions.

Best regards
Benoit
Mark RMBC
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

Post by Mark RMBC »

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?
Post Reply