Product: TIBCO Spotfire®

**Problem:**

Comparison operators (<, <=, =, >=, >) can return unexpected (but correct) results for Real values that look identical.

**Solution:**

Comparison operators (<, <=, =, >=, >) can return unexpected (but correct) results for Real values that look identical.

For example, in one row of a data table, an imported column [MeasuredValue] and a calculated column [CalculatedValue] may both display the same apparent value (17.423), even when they are formatted to display the maximum number of digits in a Table visualization, but still return "False" for the following comparison:

[MeasuredValue] = [CalculatedValue]

Why would this happen? How can the expected result ("True") be obtained?

In this example, the imported [MeasuredValue] value is exactly 17.423, but the [CalculatedValue] is not. It has a 2 in the 15th place past the decimal, instead of a 0.

This can be demonstrated by building these new calculated columns, then formatting them to show 15 places past the decimal:

[MeasuredValue] - 17.423 as [MeasuredValue minus 17.423]

[CalculatedValue] - 17.423 as [CalculatedValue minus 17.423]

[MeasuredValue] - [CalculatedValue] as [MeasuredValue minus CalculatedValue]

[MeasuredValue] = [CalculatedValue] as [MeasuredValue equals CalculatedValue]

For this example, the calculated columns shown above produce the following results:

------------

[MeasuredValue] 17.423

[CalculatedValue] 17.423

[MeasuredValue minus 17.423] 0.000000000000000

[CalculatedValue minus 17.423] 0.000000000000002

[MeasuredValue minus CalculatedValue] -0.000000000000002

[MeasuredValue equals CalculatedValue] False

------------

These results are correct for this example, even though the [MeasuredValue] and [CalculatedValue] both appear to be exactly 17.423.

For arithmetic performed on Real numbers using computers that have a finite number of bits in each byte, the least-significant digit can vary. When this happens, it is due to binary-to-decimal roundoff in the calculation. It happens in all floating-point computer calculations, not just in Spotfire.

A common way to deal with this is to round double-precision floating-point numbers (Reals) to 8 or fewer places past the decimal, before using the <, <=, =, >= and > operators to compare them.

This retains enough precision for practical use, but removes the effect of binary-to-decimal roundoff before the comparison is made.

The following example does this using the Round() function:

Round([MeasuredValue], 8) = Round([CalculatedValue], 8)

## Comments

0 comments

Article is closed for comments.