change NaN value to another value

Hi,

I want to create a computed column in a data set, which is the ratio of 2 output columns
I wrote an expression to change possible NaN result due to division by 0, but I still have NaN as a result.

Here is my expression :

value = new Number(row["C1"]/(row["C2"])*100);
if(value == Number.NaN)
{
value = 100;
}
value;

Any solution ?

Find more posts tagged with

Comments

GaryL

You can do this in the dataset query using NULLIF.

Select ["C1"] / nullif(["C2"], 0)

This will return NULL when it's divided by zero.

paulk

Comparing a variable to NaN is tricky. The function isNaN(expression) can be used. Some discussion on StackOverflow about its useage: <a class='bbc_url' href='http://stackoverflow.com/questions/2652319/how-do-you-check-that-a-number-is-nan-in-javascript'>http://stackoverflow.com/questions/2652319/how-do-you-check-that-a-number-is-nan-in-javascript</a><br />
<br />
You can also write the if statement to check the denominator for 0 instead of NaN<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>var value;
if ((row["C2"] || 0) == 0) {
value = 100;
} else {
value = new Number(row["C1"]/(row["C2"])*100);
}
value;</pre>

wwilliams

You can't use this?
BirtMath.safeDivide

This function returns the result of dividing one number by another, preventing a division by zero condition.

Syntax
BirtMath.safeDivide( dividend, divisor, ifZero )

Parameters

dividend

The number to be divided.

divisor

The number by which dividend is divided.

ifZero

The value to return when divisor is zero.

Returns

A number that results from dividing dividend by divisor.

Examples

The following example shows the results that the function returns for specific numbers:
BirtMath.safeDivide( 10, 2, 0 ) // returns 5
BirtMath.safeDivide( 10, 0, 0 ) // returns 0

The following example returns the result of dividing values in the Revenue field by values in the Volume field. If the Volume value is 0, the function returns 0.
BirtMath.safeDivide( row["Revenue"], row["Volume"], 0 )