Days360 Tm1
-
- Posts: 124
- Joined: Wed Nov 26, 2014 8:32 pm
- OLAP Product: IBM TM1, Planning Analytics
- Version: PA 2.0.5
- Excel Version: 2016
Days360 Tm1
Hi,
We are building a lease accounting calculation in tm1 .
In this calculation we would like to replicate the functionality of excel's days360 function.
Days360 in excel would give me the number of days between two dates considering a 30 month period for each month .
I can do date differnce in tm1 but is anyone tried to achieve the same as days360 in tm1 .
Any suggestions is appreciated on how to achieve this is much appreciated.
Thanks,
We are building a lease accounting calculation in tm1 .
In this calculation we would like to replicate the functionality of excel's days360 function.
Days360 in excel would give me the number of days between two dates considering a 30 month period for each month .
I can do date differnce in tm1 but is anyone tried to achieve the same as days360 in tm1 .
Any suggestions is appreciated on how to achieve this is much appreciated.
Thanks,
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Days360 Tm1
Hello,
You started another topic which is very similar to this one:
https://www.tm1forum.com/viewtopic.php? ... 267#p72267
You received good feedback but then you did not close the topic or continue with it.
What is the status of the topic ? It might give you pointers to solve this topic too.
You started another topic which is very similar to this one:
https://www.tm1forum.com/viewtopic.php? ... 267#p72267
You received good feedback but then you did not close the topic or continue with it.
What is the status of the topic ? It might give you pointers to solve this topic too.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- 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: Days360 Tm1
Jut replied to the old thread. That one was getting EO month in tm1 .
But this one looks little complex l,because the difference can be any no of years.
Thanks,
But this one looks little complex l,because the difference can be any no of years.
Thanks,
-
- MVP
- Posts: 1817
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Days360 Tm1
TM1 doesn’t have an out of the box function for this so you will need to create the logic yourself.
Rules or TI should be relatively simple. You work out the number of full months (e.g if you are looking between the 10th Jan and 15th May you would have 3 full months; Feb, Mar and Apr) then you multiply that by 30.
The bit where you need some “if” logic is on the partial months (first and last month) where the it handles what happens if there are not 30 days in that month. Should be fairly straight forward though.
As a starting point it would be beneficial to have a month dimension that has elements which are concatenation of month and year. Have attributes for the first and last day of each month in here.
The logic for the first month would be along the lines of:
If my start date is greater than 29 then use 1 day otherwise use 30 minus my start day.
For the last month something like:
If the end date is 31 then use 30 otherwise just use the day number.
Rules or TI should be relatively simple. You work out the number of full months (e.g if you are looking between the 10th Jan and 15th May you would have 3 full months; Feb, Mar and Apr) then you multiply that by 30.
The bit where you need some “if” logic is on the partial months (first and last month) where the it handles what happens if there are not 30 days in that month. Should be fairly straight forward though.
As a starting point it would be beneficial to have a month dimension that has elements which are concatenation of month and year. Have attributes for the first and last day of each month in here.
The logic for the first month would be along the lines of:
If my start date is greater than 29 then use 1 day otherwise use 30 minus my start day.
For the last month something like:
If the end date is 31 then use 30 otherwise just use the day number.
Declan Rodger
-
- 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: Days360 Tm1
HI ,
Thanks for the input .
You work out the number of full months (e.g if you are looking between the 10th Jan and 15th May you would have 3 full months; Feb, Mar and Apr) then you multiply that by 30.
How do you workout the number of full months between jan 10 2016 to jan 9 2021 .
Should i start the difference between feb 1 2016 and dec 31 2021.
Thanks for the input .
You work out the number of full months (e.g if you are looking between the 10th Jan and 15th May you would have 3 full months; Feb, Mar and Apr) then you multiply that by 30.
How do you workout the number of full months between jan 10 2016 to jan 9 2021 .
Should i start the difference between feb 1 2016 and dec 31 2021.
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Days360 Tm1
Yes. You could make use of a simple dimension containing concatenated years and months as n-level elements.
If you make sure that the elements are all in chronological order then a simple "DIMIX( dim, last month ) - DIMIX( dim, first month )" will be the heart of the solution.
Other than this, yes there will be some more date manipulations left. It isn't the most enjoyable part of TM1, IMHO, but should be feasible anyway.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Days360 Tm1
Divide the number of days between jan 10 2016 and jan 9 2021 by 30.4 and truncate the remainder. That integer amount will be the number of full months. This will work as long as the number of years between the two dates is no more than 67.
- orlando
- Community Contributor
- Posts: 167
- Joined: Fri Aug 04, 2017 8:27 am
- OLAP Product: TM1
- Version: PAL 2.0.8
- Excel Version: Office 365
Re: Days360 Tm1
hi,
something like that should work.
Date1 = jan 10 2016
Date2 = jan 9 2021
1: full month of the first year 12-Month(Date1) = 11
2: month of the full years: (Year(Date2)- (Year(Date1)+1) * 12 = 48
3: Full moth of last year: Month(Date2)-1 =0
4: now it depends what's a full month: if( day(Date1)-1 = day(date2) OR day(date1)> day(Date2)), 1,0)
Days360 in Excel gives you 59.96666667
so you need to decide if this are 59 month (step 1 to 3) or 60 (step1 to 4)
best regards,
orlando
-
- 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: Days360 Tm1
Shouldnot exceed 67 years , but curious what happens when its more than 67 . How did you come up with that number.
-
- 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: Days360 Tm1
so doy store an attribute value for each element with month number ?Wim Gielis wrote: ↑Wed Apr 17, 2019 1:00 pmYes. You could make use of a simple dimension containing concatenated years and months as n-level elements.
If you make sure that the elements are all in chronological order then a simple "DIMIX( dim, last month ) - DIMIX( dim, first month )" will be the heart of the solution.
Other than this, yes there will be some more date manipulations left. It isn't the most enjoyable part of TM1, IMHO, but should be feasible anyway.
I like this approach, so its like
200001 1
200012 12
201609 500
202109 750
etc..
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Days360 Tm1
An attribute could be a solution and not too bad, but the Dimix function gives you the index number of an element in a dimension.
So if the elements are added in the correct sequence and they remain like that, a simple difference of 2 DIMIX'es is sufficient.
So if the elements are added in the correct sequence and they remain like that, a simple difference of 2 DIMIX'es is sufficient.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Days360 Tm1
The divisor will be 1 month too long so that when you truncate the remainder it will give you one month too many. How did I figure it out? I knew it wasn't going to be perfect due to leap years and whatnot so I tested a few scenarios in Excel and 67 years was the tipping point.manu0521 wrote: ↑Wed Apr 17, 2019 6:00 pm
Shouldnot exceed 67 years , but curious what happens when its more than 67 . How did you come up with that number.
-
- 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: Days360 Tm1
Makes Sense ThanksWim Gielis wrote: ↑Wed Apr 17, 2019 6:24 pm An attribute could be a solution and not too bad, but the Dimix function gives you the index number of an element in a dimension.
So if the elements are added in the correct sequence and they remain like that, a simple difference of 2 DIMIX'es is sufficient.