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,
EOMonth Function
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: EOMonth Function
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
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
-
- Posts: 124
- Joined: Wed Nov 26, 2014 8:32 pm
- OLAP Product: IBM TM1, Planning Analytics
- Version: PA 2.0.5
- Excel Version: 2016
Re: EOMonth Function
Thanks , will give it a try .
-
- Posts: 124
- Joined: Wed Nov 26, 2014 8:32 pm
- OLAP Product: IBM TM1, Planning Analytics
- Version: PA 2.0.5
- Excel Version: 2016
Re: EOMonth Function
Hi thanks,
I was able to get this working using the below approach .
I was able to get this working using the below approach .