CompareEpsilon CompareDigits

[JokeFun]

https://help.alteryx.com/current/designer/test-functions

In Alteryx help on test functions, there're explanations for CompareEpsilon and CompareDigits with examples. However, I still failed to understand them or to implement them in the workflow. Can some one help explain in more understandable language and some more examples?

With below as an example, I don't understand in what scenarios would one need to judge if the leading digit of 0.002 is 3 places to the right of the leading digit of A which is 1. And what if we change 3 to 1 or 5 or any other numbers? How to know if I need to put 3 here or 2 instead?

While in real cases, what I need is just to Compare which number is larger.

CompareDigits(12.345, 12.347, 3) returns "True" (because the difference is 0.002, and its leading digit is 4 places to the right of the leading digit of A)

[StephenR]

@JokeFun The main purpose of these functions is to be able to compare numbers at a reasonable precision. For example, if we were comparing latitude measurements, it is unreasonable to expect a true precision of .0000000001 because that equates to 0.0001 meters which no current commercial or consumer GPS device will achieve. Sometimes computers will report at a higher precision due to floating point math (a constraint of the bitwise architecture of computers). 

Between these two functions, I would recommend using CompareEpsilon unless you are using the Fixed Decimal data type because CompareEpsilon is specifically coded to operate on floating point numbers. The use case, as stated above is to compare numbers at a specific precision. I would only use this if I am looking to see if numbers match each other at that precision. If you just need to see which is greater, I would use the greater (>) or lesser than (<) tests.

[JokeFun]

Hi @StephenR Thanks for the explanation.

So what I am after is to compare two numbers and confirm which one is larger. I encountered the problem in Table tool when I tried to set a format criteria that if the value of the cell is less than 0.3 then highlight it in yellow. And then I noticed all those 0.3 values are highlighted. And then I tried below in formula tool with [keyvalue] equal to 0.3 (at least as my eyes can see it).

if round([keyvalue],0.01)<0.3 then "true" else "false" endif.  The result is "true", not "false" as expected.

I also tries several others like

if [keyvalue]<0.30000 then "true" else "false" endif. It turns out to be "true" again.

The result changes when I change the rounding decimals or the number of zeros after 0.3. And I got to know this is a floating point number issue.

Alteryx Support suggested me to use those test functions, but I don't know how.

[PhilipMannering · Accepted solution]

CompareDigits and CompareEpsilon are ways of making sure two numbers are the same within some error.

ComapreDigits compares the first number of digits. Think significant figures. So, CompareDigits(12.345, 12.347, 3) is "True" because the first three digits of each number is 12.3 and 12.3 (the same). The speed of light is 300,000,000 or 299,792,458 depending on how precise you're being. But if we wanted to compare them to make sure they're the same we could use CompareDigits(300000000, 299792458, 3). This will evaluate to true because they are equal up to 3 significant figures (300 million each). CompareDigits(300000000, 299792458, 4) is False because you are now comparing at greater precision: 300,000,000 and 299,800,000

Compare epsilon is similar but compares the absolute difference in values. Maybe there's a rounding error and you have one source saying something cost $999.95 and another source saying something cost $1000. If we want the logic to say these are the essentially the same we could use CompareEpsilon(999.95, 1000, 0.05). This will evaluate to true because the two numbers are within 5 cents of one another.

Hope this helps.

[JokeFun]

Hi @PhilipMannering ,

Thank you for the detail explanation! It is quite straight forward and more understandable than the official notes. Thanks again!