Subtracting numbers should be 0 comes up with difference

[kierabohan]

Hello, 

I feel a little crazy with this ask - because it seems so simple, but I cannot find a similar post.  I am trying to subtract two numbers - one from an input file (Fixed Decimal), and one that I calculated in Alteryx (Fixed Decimal).  These two numbers are equal in the export to excel (comes up with a difference of 0) however when I try to calc the difference in Alteryx it comes up with a really small difference (ex - .0000000000015).  Why is this difference coming up?

[jdunkerley79 · Accepted solution]

All formula calculations in Alteryx treat numbers as doubles (even if the value is a fixed decimal).

This can lead to precision errors like you are seeing.

[ThizViz]

Just going to chime in because I dealt with a very similar issue that drove me nuts until I contacted support. In my case, I had NULLS in the data I was putting into Alteryx which was causing my results to differ from what Excel was showing. So just in case the proposed solution doesn't work, try tossing a formula into your workflow to convert NULLs to zero. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-Tool-Replace-NULL-in-all-colum...

[kierabohan]

That makes sense, is there a way to make it subtracted on fixed decimals? Or maybe I should round the numbers and then subtract.

[jdunkerley79]

No - all formulas are based on doubles I believe.

I would probably be tempted to round the result to an appropriate level of decimal places.

[MarqueeCrew]

@kierabohan,

A similar post appeared recently:  Different Results of FixedDecimal where values of 10.245 and 100.245 round to 10.24 and 10.25 when using Fixed Decimals.  I solved (in my opinion solved) the issue using a regular expression to consistently round the data after proving the precision issue using a Floor() function.

ToNumber(Regex_Replace(ToString([Before]),"(\d+)(\.\d*)",'$1')) + 
Round(ToNumber(Regex_Replace(ToString([Before]),"(\d+)(\.\d*)",'$2')),.01)

Cheers,

Mark