Page 1 of 1
EOMonth Function
Posted: Sun Apr 07, 2019 4:25 am
by manu0521
Hi,
Is there an EOMonth function available in tm1 ti . If not how do I achieve it to get the end of month date based on a given date .
I would need to get the end of month the current month and next month as well ,
Any suggestions is appreciated .
Thanks,
Re: EOMonth Function
Posted: Sun Apr 07, 2019 11:05 am
by paulsimon
Hi
You can use pretty much any of the Rule Functions in TI. Look at the Date and Time Rules Functions section of the TM1 Reference Guide.
DAYNO will get you the Date Serial Number of a string date which should be in the form YYYY-MM-DD
So if you have a date in string format such as '15-06-2019' then use SUBST to split it up
vMM = StringToNumber( subst(vDate,4,2) ) ;
vYYYY = StringToNumber( subst( vDate, 6, 4 ) ) ;
# We now need to get the first of the following month
vNextMM = vMM + 1 ;
IF( vNextMM > 12 ) ;
vNextYYYY = vYYYY + 1 ;
ELSE ;
vNextYYYY = vYYYY ;
ENDIF ;
vFirstNextMonth = NumberToString( vNextYYYY ) | '-' | vNumberToString( vNextMM ) | '-01' ;
# Subtract 1 day from the Date Serial of this to get a Date in TM1 Date Serial Format that is the end of this month
# This approach gets past the issue of months having 28, 29, 30, or 31 days.
vEOMonth = DAYNO( vFirstNextMonth ) - 1 ;
There may be a few issues in the syntax above since I haven't tried it, and this is from memory, but it should be pretty close.
Regards
Paul Simon
Re: EOMonth Function
Posted: Sun Apr 07, 2019 3:38 pm
by manu0521
Thanks , will give it a try .
Re: EOMonth Function
Posted: Mon Apr 15, 2019 2:18 am
by manu0521
Hi thanks,
I was able to get this working using the below approach .