Days360 Tm1

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

Days360 Tm1

Post by manu0521 » Sun Apr 14, 2019 3:00 pm

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,

Wim Gielis
MVP
Posts: 2017
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Days360 Tm1

Post by Wim Gielis » Sun Apr 14, 2019 5:33 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

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

Re: Days360 Tm1

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

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,

declanr
MVP
Posts: 1608
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

Post by declanr » Mon Apr 15, 2019 4:30 pm

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.

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

Re: Days360 Tm1

Post by manu0521 » Wed Apr 17, 2019 11:53 am

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.

Wim Gielis
MVP
Posts: 2017
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Days360 Tm1

Post by Wim Gielis » Wed Apr 17, 2019 1:00 pm

manu0521 wrote:
Wed Apr 17, 2019 11:53 am
Should i start the difference between feb 1 2016 and dec 31 2021.
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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

tomok
MVP
Posts: 2538
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

Post by tomok » Wed Apr 17, 2019 1:37 pm

manu0521 wrote:
Wed Apr 17, 2019 11:53 am
How do you workout the number of full months between jan 10 2016 to jan 9 2021 .
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
orlando
Posts: 70
Joined: Fri Aug 04, 2017 8:27 am
OLAP Product: TM1
Version: PAL 2.0
Excel Version: Office 365

Re: Days360 Tm1

Post by orlando » Wed Apr 17, 2019 2:41 pm

manu0521 wrote:
Wed Apr 17, 2019 11:53 am

How do you workout the number of full months between jan 10 2016 to jan 9 2021 .
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

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

Re: Days360 Tm1

Post by manu0521 » Wed Apr 17, 2019 6:00 pm

tomok wrote:
Wed Apr 17, 2019 1:37 pm
manu0521 wrote:
Wed Apr 17, 2019 11:53 am
How do you workout the number of full months between jan 10 2016 to jan 9 2021 .
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.

Shouldnot exceed 67 years , but curious what happens when its more than 67 . How did you come up with that number.

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

Re: Days360 Tm1

Post by manu0521 » Wed Apr 17, 2019 6:04 pm

Wim Gielis wrote:
Wed Apr 17, 2019 1:00 pm
manu0521 wrote:
Wed Apr 17, 2019 11:53 am
Should i start the difference between feb 1 2016 and dec 31 2021.
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.
so doy store an attribute value for each element with month number ?

I like this approach, so its like
200001 1
200012 12
201609 500
202109 750

etc..

Wim Gielis
MVP
Posts: 2017
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Days360 Tm1

Post by Wim Gielis » 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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Active form claustrophobia

tomok
MVP
Posts: 2538
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

Post by tomok » Wed Apr 17, 2019 8:21 pm

manu0521 wrote:
Wed Apr 17, 2019 6:00 pm
tomok wrote:
Wed Apr 17, 2019 1:37 pm
manu0521 wrote:
Wed Apr 17, 2019 11:53 am
How do you workout the number of full months between jan 10 2016 to jan 9 2021 .
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.

Shouldnot exceed 67 years , but curious what happens when its more than 67 . How did you come up with that number.
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.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

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

Re: Days360 Tm1

Post by manu0521 » Thu Apr 18, 2019 3:39 pm

Wim 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.
Makes Sense Thanks

Post Reply