Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Strange behavior when having fixed decimal in the expression

Samuel_To
10 - Fireball

Hi Alteryx, 

 

I am using version 2019.3.5 , and there is a strange behavior that i don't know it is a bug or not.

 

You can see that in the formula tool,

when checking 0.6==0.60 , it return false.

when checking 0.4==0.40, it return true. 

 

 

Samuel_To_0-1577686384137.png

 

When using the filter tools ,  0.6 and 0.60 all go to false,

However, for 0.4 and 0.40, all go to true. 

 

 

Samuel_To_2-1577687016300.png

Samuel_To_3-1577687175563.png

 

Overall the behavior is not aligned within Alteryx Designer when handling fixed decimal value end with 0 .

 

Best Regards,

Samuel

7 REPLIES 7
Ladarthure
14 - Magnetar
14 - Magnetar

Hi @Samuel_To,

 

I thiunk this is linked with the type of data you use, I see it as text (V_Wstring), try putting it in fixed decimal or double, it should work, I think in text it lokks for the exact string, meaning without a 0 at the end it won't work!

 

tell me if it helped, or if it did not, send a dataset so taht I can look into it!

Ladarthure
14 - Magnetar
14 - Magnetar

hi again,

 

have checked your workflow, it seems a bit odd, I do have similar behaviors on my version (2019.1), I tried a few things with random numbers and it seems to work fine, but I have to say I don't fully understand why it's not working as intended in your case.

Samuel_To
10 - Fireball

Hi @Ladarthure ,

 

I also try on 2019.3 and 2020.1 beta.

I don't think it is related to the data type, since some of the cases below are True. 

 

0.6==0.6 True

0.60==0.6 False 

0.600==0.6 False 

0.6000==0.6 False 

0.60000==0.6 True

0.600000==0.6 False

0.6000000==0.6 False

0.60000000==0.6 False 

0.600000000==0.6 True

 

 

0.4==0.4 True

0.40==0.4 True

0.400==0.4 True

0.4000==0.4 True

0.40000==0.4 True

0.400000==0.4 False

0.4000000==0.4 False

0.40000000==0.4 False

0.400000000==0.4 False

0.4000000000==0.4 True

 

Best Regards,

Samuel

MarqueeCrew
20 - Arcturus
20 - Arcturus

Fixed decimals are something that I use only on output. I'd recommend that for your comparison, that you use a tonumber() function. 

fixed decimals are the only variable precision data type and this oddity I believe is a known behavior. 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
JoeS
Alteryx
Alteryx

Hi,

 

I believe this is going to be due to the way in which calculations are performed in Alteryx on the data types of doubles, or floats.

 

There is a great article here that explains it: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Floating-point-numbers-are-surprisi...

 

 

Samuel_To
10 - Fireball

Hi @MarqueeCrew ,

 

The behavior is the same after using tonumber.

 

if tonumber(0.600)==tonumber(0.6)
then 'True'
else 'False'
endif

 

Samuel_To_0-1577697830014.png

 

Best Regards,

Samuel

ChrisK
Alteryx
Alteryx

@Samuel_ToWe have changed the way we converted text to numbers in our formula processor. It is still a bad idea to compare floating point numbers for exact equality, because the way they are computed can lead to almost invisible differences. Even so, it was annoying that 0.6 didn't come out to be the same number as 0.600. We fixed this in version 2022.1. Can you give it a try and see what you get?

ChrisK_0-1669852670832.png

 

The way this can happen is that with "0.6", somewhere in the conversion it computes 6/10 to get the value you want. With "0.600" it does the computation 600/1000. It happens that those two computations didn't come out to be exactly the same. It could be that it really computed 600 / 10 / 10 / 10, for example. They way the computation is done makes a critical difference.

 

The code change we made in 22.1 uses the same conversion from text to numbers in the formula library as is used elsewhere in Alteryx code. This increases your odds that numbers that look the same will actually be the same. However, it is never safe to trust that two different paths that should give the same number really will give exactly the same number.

Labels