value compare issue ><=

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.

Developer

Preparation

Accepted answers

JokeFun

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

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Floating-point-numbers-are-surprisingly-strange/ta-p/29049

All comments

Jonathan-Sherman

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

grossal

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

jdunkerley79

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

JokeFun

@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.

JokeFun

Hi @grossal , my version is 2019.2

JokeFun

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

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Floating-point-numbers-are-surprisingly-strange/ta-p/29049

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167