ROUNDUP Function in TM1 Rules

Post Reply
Rams
Posts: 21
Joined: Thu Mar 14, 2013 12:29 am
OLAP Product: TM1_Cognos
Version: 9.5.2_10.1_10.2
Excel Version: 2007
Location: Sydney, Australia

ROUNDUP Function in TM1 Rules

Post by Rams » Tue Aug 20, 2013 11:53 pm

Hi,

In TM1 Rules, I'm subtracting two dates to get the remaining months to forecast, first converting the date into DAYNO then converting into months. In this case say for eg in excel. Today 21/08/2013 >> 41507 & Completion Date 1/10/2013 >> 41548

x = (41548 - 41507) = 41 days = 12*(41/365) = 1.347

If in excel I can use ROUNDUP(1.347) to get 2, but in TM1 has ROUND & ROUNDP which was not doing a favour.

I got some more information about ROUND function in this http://pic.dhe.ibm.com/infocenter/ctm1/ ... round.html, but nothing works. Anybody has come across this situation, please help.

Code: Select all

ROUND(12*(((DAYNO(ATTRS('Site', !Site, 'Completion Date'))) - (DAYNO(TimSt (NOW,'\Y-\m-\d'))))/365 )),2,0)
Thanks in Advance.

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by EvgenyT » Wed Aug 21, 2013 1:39 am

Correct me if Im wrong, but from your example "to get the remaining months to forecast" is the difference between two months numbers i.e Oct - Aug. If so, why dont you just subtract one from another?

Thanks

ET

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by EvgenyT » Wed Aug 21, 2013 1:51 am

Or perhaps you can try that:

IF(INT(x)<x, x+1,INT(x) ).... basically saying IF Integer of the value X (which is 1) < X (1.347) than add 1, which gives you 2, if its greater e.g. 1.7 then get INT(X) gives you 2 anyway.

Think that what you need?

Thanks
ET

Rams
Posts: 21
Joined: Thu Mar 14, 2013 12:29 am
OLAP Product: TM1_Cognos
Version: 9.5.2_10.1_10.2
Excel Version: 2007
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by Rams » Wed Aug 21, 2013 4:22 am

Thanks for your reply EvgenyT.

1. You are right I can easily get the months from both dates and subtract, but in worst case if the current month in Dec and my Completion month in March in that case i get x = 12 - 3 = 9, but I need 3. So I opt to go with DAYNO.

2. Bang! we both are in same page now. I've created similar IF condition to round up the numbers, but this part is already in nested IF loop which has almost 6 conditions to check before it come over here, So I hesitate to put one more IF condition for this logic, I thought there would be some function in TM1 which gives me a similar result like ROUNDUP in excel.

If there is no such thing then my least option is to go with IF condition.

User avatar
Martin Ryan
Site Admin
Posts: 1957
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: ROUNDUP Function in TM1 Rules

Post by Martin Ryan » Wed Aug 21, 2013 5:00 am

I usually just add 0.49999 and then round it. That will ensure a figure is always rounded up. It appears less precise, but I've never seen it not work. It's also slightly faster than an IF statement.

['x'] = round(['y'] - ['z'] + 0.49999);

NB the reason it's 0.49999 and not 0.5 is because if you add 0.5 to an integer then it gets rounded up 1, whereas 0.4999 ensures it stays as is. E.g round(1+0.4999)=1 whereas round(1+0.5)=2.

HTH,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by EvgenyT » Wed Aug 21, 2013 5:16 am

Rams wrote:If there is no such thing then my least option is to go with IF condition.
I dont think its possible ...TM1 uses the Round down method of floor(x + .5). Please correct me if I'm wrong Martin.

or you can do as Martin says :) Always try to avoid unnecessary IF's ..

Thanks

ET

Rams
Posts: 21
Joined: Thu Mar 14, 2013 12:29 am
OLAP Product: TM1_Cognos
Version: 9.5.2_10.1_10.2
Excel Version: 2007
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by Rams » Wed Aug 21, 2013 6:46 am

['x'] = round(['y'] - ['z'] + 0.49999);
oh...that's Kewl one Martin!!! Let me give a try!
TM1 uses the Round down method of floor(x + .5)
You are right EvgenyT, Floor & Ceiling functions are Excel ones.

Thanks a lot EvgenyT & Martin!!

Duncan P
MVP
Posts: 588
Joined: Wed Aug 17, 2011 1:19 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2
Excel Version: 2003 2007
Location: York, UK

Re: ROUNDUP Function in TM1 Rules

Post by Duncan P » Wed Aug 21, 2013 12:52 pm

Code: Select all

['roundup'] = - INT( - ['value'] );

EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by EvgenyT » Wed Aug 21, 2013 11:09 pm

Neat :)

Rams
Posts: 21
Joined: Thu Mar 14, 2013 12:29 am
OLAP Product: TM1_Cognos
Version: 9.5.2_10.1_10.2
Excel Version: 2007
Location: Sydney, Australia

Re: ROUNDUP Function in TM1 Rules

Post by Rams » Wed Aug 21, 2013 11:38 pm

['roundup'] = - INT( - ['value'] );
:o That's Beauty!! Thanks Duncan P!! Working Good ;)

Post Reply