Sum and summary tool give wrong value (1.3322e-15) instead of zero (0)

Question

I have data with the double datatype. Values are all with two (2) decimals, and the sum should be zero (0). Instead of a correct value data profiler gives the value for Sum 1.3322e-15. The same problem is with the summary tool if I group with the key field.

The problem is still the same even if I use the round(field, .01) function or date type is fixed decimal.

Even more confusing is that with the same dataset, some rows Alteryx calculated correctly, but some not.

This is calculated correctly.

esar · Answer

Hi @danilang

Here is my input data.

The id 2103 has the problem but 2126 does not.

Summary_problem.yxdb

Kamran1991 · Answer

Please check and correct the data type in the formula tool

danilang · Answer

Hi @esar

These differences are very small and can probably be explained by the floating point representation issue.  Floating point number are only accurate to about 15 decimal digits so errors of this size are normal.   Check out the Representation Error section for more details.  This article is written for Python, but the principles apply to any floating point math.

Excel is a special case.  Since it was developed for financial applications, it has string based calculation libraries as opposed to most languages that use floating point libraries.  The string based libraries don't suffer from the floating point representation errors, but they are much slower.

Can you share you input data?

Dan