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 .