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. Importantly, both work in both TI processes and rules.
Also, if you are looking for a ROUNDUP function in TM1, please see this post!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.142+0.5 and take off the decimals to get 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.
The TI to do this is this:
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.
And also with a precision of 0. Note the same result from using ROUND, where TM1 has rounded to the nearest whole number.
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.
And the TI that produces this is it with 1 decimal place is this:
Notes about these Functions
Both these functions are able to be used in both TM1 rules and Turbo Integrator processes.
