How To Convert Infinity, -Infinity, NaN to Zero Value
Sometimes in the numeric formatted cells, as a result of various calculations, it is possible to have returned as a result the Anaplan special numbers: Infinity, -Infinity, or NaN (Not a Number).
If the numeric line item with this issue is also an input for other formulas, the final result could be altered. To solve this issue a solution is to convert these Anaplan special numbers in zero values.
Convert Infinity and -Infinity:
First thing is to have calculated the "Infinity" value in a system module without any lists.
This can be achieved in different ways by applying one of the formulas in a numeric formatted line-item: DIVIDE(1,0) or VALUE("Infinity").
Then it can be applied, in sequence, to the following Anaplan functions:
Convert numeric line-item in positive number (function: ABS).
Compare the result in Step 1 if it is NOT equal with the Infinity value calculated in the system module
If TRUE then consider the numeric value, ELSE consider 0
Example: IFABS(Value num) <> 'SY00 Params'.'Val. Infinity' THEN Value num ELSE 0
Note: ABS function is useful to solve in one comparison both Infinity (positive) and -Infinity (negative)
Convert NaN: Apply in sequence the following Anaplan functions/statements:
Convert numeric line-item in positive number (function: ABS)
Compare result Step 1 if it is greater or equal with zero. NaN value will return false.
Consider the initial value of the line-item if Step 1 is greater or equal with zero else consider zero (on ELSE the NaN will be converted to zero).
Example: IF ABS(Value num) >= 0 THEN Value num ELSE 0
- ABS function is useful to solve in one comparison both positive and negative numbers
- NaN=NaN, NaN>=0, NaN<=0 comparisons will always return FALSE. This is why it is needed to check with zero the numeric values.
The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.