So I have met a strange scenario where I have a list of numbers already rounded to 6 decimals and I am trying to compare those values with a specific number like 0.3.
Below are the formulas I have for the [KeyValu] field which is in fixed decimal format (19.6).
if [KeyValue]<0.3 then "True" else "False" endif, result for value 0.3 showing True
and then I tried to round the field and had below interesting result.
if round([KeyValue],0.1)<0.3 then "True" else "False" endif, result for value 0.3 showing False
if round([KeyValue],0.01)<0.3 then "True" else "False" endif, result for value 0.3 showing True
if round([KeyValue],0.001)<0.3 then "True" else "False" endif, result for value 0.3 showing True
if round([KeyValue],0.0001)<0.3 then "True" else "False" endif, result for value 0.3 showing True
if round([KeyValue],0.00001)<0.3 then "True" else "False" endif, result for value 0.3 showing False
if round([KeyValue],0.00001)<0.3 then "True" else "False" endif, result for value 0.3 showing True
..., result for value 0.3 showing True
if round([KeyValue],0.000000001)<0.3 then "True" else "False" endif, result for value 0.3 showing False
...
And then I also tried below.
if [KeyValue]<0.30 then "True" else "False" endif, result for value 0.3 (in 6 decimals which shows as 0.300000 in alteryx) shows False
if [KeyValue]<0.300 then "True" else "False" endif, result for value 0.3 (in 6 decimals which shows as 0.300000 in alteryx) shows False
if [KeyValue]<0.3000 then "True" else "False" endif, result for value 0.3 (in 6 decimals which shows as 0.300000 in alteryx) shows False
if [KeyValue]<0.30000 then "True" else "False" endif, result for value 0.3 (in 6 decimals which shows as 0.300000 in alteryx) shows True
if [KeyValue]<0.300000 then "True" else "False" endif, result for value 0.3 (in 6 decimals which shows as 0.300000 in alteryx) shows False
Does anyone know the tricks here? Thanks.
Solved! Go to Solution.
Hi @JokeFun,
It could be your 0.3 value is needs to be rounded to 2 decimal places first
Round([Field1],0.01)
if Round([KeyValue],0.01)<0.3 then "True" else "False" endif
if Round([KeyValue],0.01)=0.3 then "True" else "False" endif
If this solves your issue please mark the answer as correct, if not let me know!
Regards,
Jonathan
Hi @JokeFun,
I tried the same rounding as you did and it actually delivers the same results, what really surprises me.
Do you also use 2020.1 or do you have the bug in a different version? To me this is a bug in the Software. If nobody comes up with a clutch solution or explanation, you should contact support@alteryx.com and describe the issue. This has to be fixed. Many calculations depend to the correctness of similarity and/or greater/less than something.
Best
Alex
It's worth noting that Alteryx treats numbers as doubles in the formula tool, even though you have chosen a decimal it is evaluated as a double.
Double arithmetic does have epsilon errors, but am surprised any difference between the different 0.3 with trailing 0s
@Jonathan-Sherman Hi, I have already rounded the values to 6 decimals. And as I was trying to say, the result changes when you compare the values to 0.3, 0.30, 0.300, 0.3000, 0.30000 and so on so forth.
Or I further round the 6 decimals down to 1~5 again, the result still changes for each.
Hi @grossal , my version is 2019.2
Alteryx replied this is due to double format in calculation. Test function is suggested for such cases.
https://help.alteryx.com/current/designer/test-functions
https://help.alteryx.com/2020.1/FAQ.htm?Highlight=CompareDigits