topic Re: Qlik decimal issue in Management & Governance

Qlik decimal issue

giuseppedicosmo — Wed, 14 Dec 2022 14:11:24 GMT

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?

Re: Qlik decimal issue

Mark_Winter — Wed, 14 Dec 2022 15:00:48 GMT

Hi Giuseppe,

Like this?

Re: Qlik decimal issue

giuseppedicosmo — Wed, 14 Dec 2022 15:04:16 GMT

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

Re: Qlik decimal issue

Mark_Winter — Wed, 14 Dec 2022 15:49:08 GMT

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

Re: Qlik decimal issue

giuseppedicosmo — Wed, 14 Dec 2022 16:01:55 GMT

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!

Re: Qlik decimal issue

Or — Wed, 14 Dec 2022 16:28:19 GMT

Perhaps this will shed some light on the problem:

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

Re: Qlik decimal issue

hic — Wed, 14 Dec 2022 16:50:38 GMT

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.

Re: Qlik decimal issue

giuseppedicosmo — Thu, 15 Dec 2022 09:02:24 GMT

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?

Re: Qlik decimal issue

hic — Thu, 15 Dec 2022 09:51:31 GMT

There are two good work-arounds:

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)
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