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 » Thu Oct 04, 2018 2:41 pm

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
Community Contributor
Posts: 239
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Using Dates And Times In TM1

Post by PavoGa » Thu Oct 04, 2018 9:32 pm

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: 1808
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Getting the last date of a month in TI

Post by Wim Gielis » Fri Oct 05, 2018 4:33 am

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

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

User avatar
PavoGa
Community Contributor
Posts: 239
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0
Excel Version: 2013
Location: Cleveland, Tennessee

Re: Getting the last date of a month in TI

Post by PavoGa » Fri Oct 05, 2018 1:02 pm

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: 17
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 » Mon Oct 08, 2018 6:11 pm

Code: Select all

    nSerialDate = ParseDate( sYear | '-' | sMonth | '-01',  'yyyy-M-dd' );
    nSerialDate = nSerialDate - 1;
    sLastDayPriorMonth = FormatDate( nSerialDate, 'yyyy-MM-dd' );
    

Wim Gielis
MVP
Posts: 1808
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Getting the last date of a month in TI

Post by Wim Gielis » Mon Oct 08, 2018 8:04 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Wim Gielis
MVP
Posts: 1808
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Getting the last date of a month in TI

Post by Wim Gielis » Mon Oct 08, 2018 8:05 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

ndivine
Posts: 17
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 » Mon Oct 08, 2018 8:42 pm

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 » Tue Oct 09, 2018 12:07 pm

@ndivine: wow!

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

Thanks to all contributers for the suggestions.

Best regards
Benoit

Mark RMBC
Regular Participant
Posts: 160
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 » Mon Oct 15, 2018 10:10 am

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