Dataflow resulting in small digit differences
Hello,
i have an unexpected behaviour with a Dataflow.
Performing a simple calculation, C = A + B, a double cube is somehow getting tiny amounts. All cubes are Double type. Representing the information in a dataview, all rows only have numbers with 2 decimal places: yet the subtotal shows very small digits differences (e.g. -0.000000000003637979).
Do you know why this could happen?
Thanks,
Answers
-
Hello,
Every sum algorithm is based on binary numbers, that's why you might incur in situations where a sum has those little discrepancies. Try to put a ROUND function in your dataflow when calculating C block to avoid this issue.
e.g.
C=ROUND(A+B,2) > this should round your numbers to the second decimal digit and solve the issue.
Considering the situation in a broader perspective, we can say that a number with a finite number of decimal digits in decimal base, may have an infinite number of decimal digits in its binary expression.
Moreover, the floating point (applied in BOARD) gives extra precision to the result when it's zero because it uses less digits for the non-decimal part. Since almost every computing engine (and this includes BOARD) uses binary expressions of numbers and finite number of decimal digits, the above consideration should be taken into account when handling very large numbers in each cell (trillions/billions with high number of decimals).Hope this helped,
Antonio Speca
Product specialist
11 -
Thank you Antonio.
We've done some research and agree that this is a widespread problem that also affects Microsoft Excel.
Microsoft Knowledgebase Article
This link to Microsoft Support site explains the issue as follows:
"The IEEE 754 standard is a method of storing floating-point numbers in a compact way that is easy to manipulate. This standard is used by Intel coprocessors and most PC-based programs that implement floating-point math.
IEEE 754 specifies that numbers be stored in binary format to reduce storage requirements and allow the built-in binary arithmetic instructions that are available on all microprocessors to process the data in a relatively rapid fashion. However, some numbers that are simple, nonrepeating decimal numbers are converted into repeating binary numbers that cannot be stored with perfect accuracy.
For example, the number 1/10 can be represented in a decimal number system with a simple decimal:
.1
However, the same number in binary format becomes the repeating binary decimal:
.0001100011000111000111 (and so on)
This number cannot be represented in a finite amount of space. Therefore, this number is rounded down by approximately -2.78E-17 when it is stored.
If several arithmetic operations are performed to obtain a given result, these rounding errors may be cumulative."Thanks
7 -
Floating point accuracy is indeed a downside of using a Single or Double datatype. One alternative could be using Integer datatypes and store values at a multiple of their significant value.
0 -
hi
Integer would be a bad choice, cause it's from -32.000 to +32.000 (approx.) so if you want to use it for big numbers, those can't be used. Furtermore using Versions in Integer Cubes can lead to some (technical correct) behaviors, cause on aggregated level the number is to big to be stored in an Integer Cube
Regards
Björn
2
