EOMonth Function

Post Reply
manu0521
Posts: 39
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2010

EOMonth Function

Post by manu0521 » Sun Apr 07, 2019 4:25 am

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,

User avatar
paulsimon
MVP
Posts: 683
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: EOMonth Function

Post by paulsimon » Sun Apr 07, 2019 11:05 am

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

manu0521
Posts: 39
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2010

Re: EOMonth Function

Post by manu0521 » Sun Apr 07, 2019 3:38 pm

Thanks , will give it a try .

manu0521
Posts: 39
Joined: Wed Nov 26, 2014 8:32 pm
OLAP Product: TM1
Version: 9.5
Excel Version: 2010

Re: EOMonth Function

Post by manu0521 » Mon Apr 15, 2019 2:18 am

Hi thanks,

I was able to get this working using the below approach .

Post Reply