I have recently found a bit of a quirk with the standard filter tool.
I was trying to filter out values equal to zero, but found that each time I ran the workflow some fields populated '0' were not being filtered out. Even more confusing was the fact that each time I ran the workflow I got a different number of records passing through, even though the data is identical each time.
So, it seems as though Alteryx is identifying different records as being non zero each time the workflow is run. This in turn affects ,calculations further down stream.
Luckily the solution is simple - change the data type from double to fixed decimal. Now the filter tool successfully removes all zero records every time the workflow is run.
The main reason for mentioning this is that many people will be unaware of this bug and knowing about it may save you a lot of time. I spent ages checking through the workflow to see why I was getting different results each time I ran it!
This is not a bug, not limited to the Filter tool, and generally not "caused" by Alteryx.
The root cause is:
Since this is an issue with all computers, Microsoft explains the issue for Excel users in the following article: https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...
One of their examples: Enter the following into a new workbook:
A1: =(43.1-43.2)+1
Right-click cell A1, and then click Format Cells. On the Number tab, click Scientific under Category. Set the Decimal places to 15.
Instead of displaying 0.9, Excel displays 0.899999999999999. Because (43.1-43.2) is calculated first, -0.1 is stored temporarily and the error from storing -0.1 is introduced into the calculation.
The only way to correct this is to stick with integer math, as all base-10 integers can be represented correctly in base-2.
You will likely experience unique challenges when processing data type Double, or when using the Formula tool, or the ROUND function.
Examples:
Round(1.25, 0.1) = 1.3 Correct: rounds UP as expected
Round(1.575, 0.01) = 1.58 Correct: rounds UP as expected
Round( 1.255, 0.01) = 1.25 Incorrect. Should round UP to 1.26
206.95 when stored as a double in binary is actually '206.94999999999998863131622783839702606201171875' , and when rounded at the first decimal place becomes 206.9
1.45 when stored as a double in binary is actually '1.4499999999999999555910790149937383830547332763671875' and when rounded at the first decimal place becomes 1.4
1.55 when stored as a double in binary is actually '1.5500000000000000444089209850062616169452667236328125' and when rounded at the first decimal place becomes 1.6
MyNumber | Formula Used | Result |
4.717949 | ROUND([MyNumber], 0.1) | 4.7 |
4.35 | ROUND([MyNumber], 0.1) | 4.3 |
4.510204 | ROUND([MyNumber], 0.1) | 4.5 |
4.283757 | ROUND([MyNumber], 0.1) | 4.3 |
References (some of these may be archived):
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Formula-Round-4-975-0-01-getting-4-97-...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Rounding-Down-to-first-Decimal/td-p/24...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Rounding-problems/td-p/486194
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/another-rounding-question/m-p/423279#M...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/another-rounding-question/m-p/423303#M...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Alteryx-Odd-Behavior/td-p/89086)
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Calculations-with-Field-Type-Int64/td-...
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Filter-Doesn-t-Seem-to-be-Working-Corr...
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Big-numbers-and-Alteryx/ta-p/105952
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/numbers-outputting-quot-3-7E-11-quot-a...
https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...
I avoid data type Double, when possible.
Chris
@ChrisTX
Thanks for the very infromative reply.