value compare issue ><=

[JokeFun]

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.

[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 · Accepted solution]

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