Rounding in TM1 – The Complete Guide!

Rounding is where we remove decimal places from a number and force the last remaining decimal place or integer up or down depending on the source value. In TM1, there are two functions for this – ROUND and ROUNDP. They do similar, but different things and work similarly, but not the same as the corresponding function in Excel.

What are ROUND and ROUNDP in TM1?

The TM1 ROUND Function

The ROUND function in TM1 takes a value, adds 0.5 and then removes all decimal places. So, using it in a Turbo Integrator process will change 3.142 to just plain old 3 (as it would be the floor of 3.142 + 0.5, which is 3). However, if you had 3.50001, it would be changed to 4 (3.50001+0.5, therefore 4). The method in a TI is different from Excel, where Excel uses a “round to nearest”, but the results are almost identical.

Syntax

The syntax is simply:

ROUND(vValue);

Where vValue is a number. Note there is no precision indicator as it will round down the value to nearest integer after adding 0.5 to it.

Example in a TI

Here is an example. This first screenshot is with the data as per the source. You can see there are multiple values with varied decimal places.

This then is the same view after running the TI with the ROUND function. You can see that the left side of the numbers is identical to the first screenshot, but they have had 0.5 added to them and all digits to the right of the decimal point have then been stripped off.

This function in TM1 works differently to the corresponding Excel function in that it does not allow you to define the number of decimal places. It only goes to a whole digit, after adding 0.5 and removing the digits beyond the decimal place.

TM1 rounding using ROUND results

The TI to do this is this:

TM1 ROUND Turbo Integrator process

The TM1 ROUNDP Function

ROUNDP is the TM1 function that works similarly to Excel, in that you can define the precision to round a number to and it will round to the nearest value. For example, if you use it on 3.142 and round it to two decimal places, you will get 3.14. One decimal place, you’ll get 3.1, etc.

It also allows you to round further than a whole digit. For example, if you have 11.67 and round it to a precision of minus 1 (-1), then your result will be 10. If you had 10,367.899 and used it with -2 precision (hundreds), you would get 10400.

Syntax

The syntax is:

ROUNDP(vValue,precision);

Where vValue is the number to be rounded and precision is the number of decimal places to round it to.

Example

Here is the same screenshot from above using ROUNDP, with a precision of 1.

TM1 rounding using ROUNDP. Results with 1 precision.

And also with a precision of 0. Note the same result from using ROUND, where TM1 has rounded to the nearest whole number.

TM1 rounding using ROUNDP. Results with 0 precision.

Finally, with a precision of -1. Note here the last two digits of every number are 0 (i.e. it is rounded to the nearest 10), showing the impact of this precision.

TM1 rounding using ROUNDP. Results with minus 1 precision.

And the TI that produces this is it with 1 decimal place is this:

TM1 ROUNDP Turbo Integrator process

Notes about these Functions

Both these functions are able to be used in both TM1 rules and Turbo Integrator processes.

If you like this post, please spread the love…

About John

Managing Director of Infocube. We are a management accounting consultancy that specialises in Business Analytics.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.