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?
Solved! Go to 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.
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.
That makes sense, is there a way to make it subtracted on fixed decimals? Or maybe I should round the numbers and then subtract.
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.
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