Qlik decimal issue

[giuseppedicosmo]

Hi,
We have a problem on QlikSense. We tried to do this equality: 0.3=3/10 and we got a false result. We got the same result for 0.6 and 0.9 too, as you can see in the attached file. This can be a problem for kpi that use this numbers. Morever the constant value is less than fract value. The script that we wrote is:
"LOAD
    Num#('0,'&RecNo(),'#,#') as const,
    Num(RecNo()/10,'#,#') as fract
AutoGenerate 9;"
But we tried to replicate the same issue in frontend without fields only doing: 0.3=3/10.  What can we do?

[Mark_Winter]

Hi Giuseppe,

Like this?

[giuseppedicosmo]

Yes, but i have false for 0.3,0.6 and 0.9. Can i see the formule that do you use for the equality? For me is 0.3=3/10 or const=fract

[Mark_Winter]

It's const=fract but, before then, Num#(RecNo()/10,'#,#') as fract

[giuseppedicosmo]

Hi Mark, 

Thanks.

But if you use num# you force the argument to be a string. I need to understand why 0.3 as string is different from 3/10, but this doesn't happen for 0.2,0.1 etc. The problem is that i have a sum of a field that is 0.1+0.1+0.1 and this is different from 3/10. This is very strange, because if i do sum(field)=0.3 i got false, even if the sum is 0.3! 

[Or]

Perhaps this will shed some light on the problem:

https://community.qlik.com/t5/Design/Rounding-Errors/ba-p/1468808

[hic]

You should be very careful when you use equality as condition for floats. Read Or's link above, and you'll understand.

Internally in the Qlik engine we now (introduced after the above blog post) have an algorithm that tries to evaluate if the two numbers are "close enough" and if so, return TRUE. But if we were to be strict, comparing two floats would almost always result in FALSE.

[giuseppedicosmo]

Hi @hic , the article is very good! Now it's all clear, but why do i have that the fixed number is less than fract number? How is it possible? Because you say that this is a problem on equality, but i have problems on disequality too. And if i need to set an equality for a project, what can i do for workaround this issue?

[hic · Accepted solution]

There are two good work-arounds:

1. Convert it to integers: If you for example know that you always work with tenths (1/10) then you can test
   Round(10*const)=Round(10*frac)
2. Determine a "close enough"-distance: If you assume that a difference of 0.000001 is close enough, then you can do the test as
   fAbs(const-frac)<=0.000001
   or if you want relative difference, you could use the following test:
   fAbs(const-frac)/RangeMax(fAbs(const),fAbs(frac))<=0.000001