How to write a custom expression to achieve a similar functionality as that of Excel's Trunc() function in TIBCO Spotfire

How to write a custom expression to achieve a similar functionality as that of Excel's Trunc() function in TIBCO Spotfire

Trunc function in Excel or other tools will truncate a decimal number to a specified position after the decimal point. It will not round up the number, it will just truncate it.

For example, trunc(4.99,1) will evaluate to 4.9, trunc(4.487,2) will evaluate to 4.48, and likewise.

As of today, trunc() function is not available in Spotfire. This article specifies the custom expression to achieve the same functionality along with a brief explanation. Following is the required expression:

Integer([Data] * 10^N)/10^N

N is the position after decimal point till where you want to truncate the number. After this, format the new column to N decimals.

Explanation: Suppose you have a number 76.996 and you want to truncate it till one (N = 1) place after the decimal point.

1. Multiply this number by 10^1 (Result = 769.96).
2. Convert it to integer using Integer() function (Result = 769)
3. Divide this number by 10^1 (Result = 76.90).
4. Change the formatting of this column to one (since N=1) decimal place (Result = 76.9).