How to write a custom expression to achieve a similar functionality as that of Excel's Trunc() function in TIBCO Spotfire
Article ID: KB0075535
Updated On:
| Products | Versions |
|---|---|
| Spotfire Analyst | 7.11 and Higher |
Description
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.
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.
Issue/Introduction
How to write a custom expression to achieve a similar functionality as that of Excel's Trunc() function in TIBCO Spotfire
Environment
All Supported Operating Systems.
Resolution
Following is the required expression:
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.
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.
- Multiply this number by 10^1 (Result = 769.96).
- Convert it to integer using Integer() function (Result = 769)
- Divide this number by 10^1 (Result = 76.90).
- Change the formatting of this column to one (since N=1) decimal place (Result = 76.9).
Feedback
Yes
No
Powered by
