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