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

Product:TIBCO Spotfire Analyst
Versions:All supported versions

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

Details:
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?
 

Resolution:
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)