We often get questions regarding strange calculation results when using decimal numbers in Web Intelligence.
Typically:
- Why don't I get 0 when I do A + B - C, although C is equal to A + B?
- Why is the result of this running sum different, when I change the sorting order of my table?
- Etc.
These strange calculation results are due to the fact that, internally, Web Intelligence represents decimal numbers using the 64-bit precision version of the Floating Point data type defined by the IEEE 754 standard.
A detailed explanation of that standard can be found in the following Wikipedia page: http://en.wikipedia.org/wiki/IEEE_floating_point.
With the IEEE 754 standard on floating point arithmetic, decimal numbers cannot be perfectly represented in binary form, since not all their digits can be trusted. In fact, the number of digits which can be trusted actually depends on the size of the representation.
With the 64-bit representation (also called "double precision") used in Web Intelligence, the number of bits used for stocking the data is 53 and, as explained in the above Wikipedia page, the number of digits which can be trusted is: log(2^53), rounded to 15.
IMPORTANT TO REMEMBER: In Web Intelligence, the maximum number of digits which can be trusted in a decimal number is 15 and that takes into account digits both before and after the decimal mark.
Example:
- 100,000,000,000 + 0.001 will correctly result in 100,000,000,000.0010 because this decimal number only requires 15 trusted digits to be represented (12 digits before the decimal mark and 3 after).
- 100,000,000,000 + 0.0001 will wrongly result in 100,000,000,000.0000 because the correct result would require 16 trusted digits to be represented (12 before the decimal mark and 4 after)
With this limitation in mind, summing decimal numbers might not always give the expected result, especially if the expected result is 0...!
For example, summing 19000.2, 0.123, -9100.3, -0.000000000002543, etc. will allow for only 10 digits after the decimal mark to be trusted. Indeed, 5 digits before the decimal mark are already used by the largest number 19000.2. Consequently, if the result of that sum is something like: -0.000000000013327, it will be indistinguishable from 0, by the IEEE 754 standard.
Therefore, if a 0 result from a decimal numbers calculation is used as a condition for further processing in a Web Intelligence document, it is highly recommended to convert the decimal numbers into integers before that condition is evaluated. This rounding operation can be done with the Round(number; round_level) formula, using 0 for the round_level parameter.
Now, why is changing the sorting of a table might also change a decimal calculation result?
Well... Another major limitation of the IEEE 754 floating point format is that the integer part of decimal numbers is represented by a fraction and is therefore approximated. As a consequence, there will be a rounding error which will be propagated along the calculation, leading to different results according to the way the values are sorted.
Example:
Say, we have A = 1,000,000, B = 1.2 and C = -1,000,000
- A + B + C will result in 1.19999999995343E0
- A + C + B will result in 1.2E0
This is because the rounding error does not propagate the same way through A + B + C, than it does through A + C + B.
I hope these "strange calculation results" will be clearer now! :smile:
If not, please do not hesitate to ask questions below.
Best regards,
Pascal.
