Page 1 of 1

Getting the last date of a month in TI

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

Re: Using Dates And Times In TM1

Posted: Thu Oct 04, 2018 9:32 pm
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.

Re: Getting the last date of a month in TI

Posted: Fri Oct 05, 2018 4:33 am
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 ?

Re: Getting the last date of a month in TI

Posted: Fri Oct 05, 2018 10:20 am
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

Re: Getting the last date of a month in TI

Posted: Fri Oct 05, 2018 1:02 pm
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.

Re: Getting the last date of a month in TI

Posted: Mon Oct 08, 2018 6:11 pm
by ndivine

Code: Select all

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

Re: Getting the last date of a month in TI

Posted: Mon Oct 08, 2018 8:04 pm
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' );

Re: Getting the last date of a month in TI

Posted: Mon Oct 08, 2018 8:05 pm
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

Re: Getting the last date of a month in TI

Posted: Mon Oct 08, 2018 8:42 pm
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.

Re: Getting the last date of a month in TI

Posted: Tue Oct 09, 2018 12:07 pm
by Benoit
@ndivine: wow!

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

Thanks to all contributers for the suggestions.

Best regards
Benoit

Re: Getting the last date of a month in TI

Posted: Mon Oct 15, 2018 10:10 am
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?