ROUNDUP Function in TM1 Rules

[Rams]

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]

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]

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]

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.

[Martin Ryan]

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

[EvgenyT]

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]

['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]

Code: Select all

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

[EvgenyT]

Neat

[Rams]

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

That's Beauty!! Thanks Duncan P!! Working Good