Comparing Result of Division Expression with float

[christianfudge]

Hi,

I've uncovered what I believe is a bug when trying to compare the result of a division expression with a float. I initially uncovered it in a very complex formula with a very complex model but I've managed to finally reproduce what I believe is the core of the problem in the simplest possible model. I'm hoping if someone can explain what's going on here then I'll be able to fix the bigger problem.

Please find attached a very simple 25 line table with 2 columns.

The sum of column2 is 20, the sum of column1 is 25.

20/25 = 0.8.

But when I say If(result=0.8,1,0) I always get a 0 when in fact it should be a 1?

If I replace the expression with the figures (e.g 20/25) then it correctly returns a 1.

Can someone please explain what's going on here?!

[m_woolf · Accepted solution]

Qlik Design Blog : Rounding Errors | Qlik Community

[petter]

Yes you flunked 😉

However if you do this you will be able to get the logic correct:

=Round(Sum(TARGET) / Sum(FLAG),0.00001)=0.8

[christianfudge]

Thanks for the info, I came across something similar with dates. I only have one issue with the post you referenced...

"And finally, you should never use equality as a condition when comparing floats. Use greater than or less than".

If you change my expression to =if((Sum(TARGET)/Sum(FLAG))>0.8,1,0) it incorrectly returns a 1. I now know this is due to the hexadecimal representation of the result but still, the advice is incorrect isn't it?

I'm happy that I have a solution at least..... although I dread to think of the performance impact of rounding every result multiplied with 100 in all expressions dealing with decimals....

Thanks for the quick reply, very helpful.

[christianfudge]

Thanks for the addition! I much prefer using one round function rather than rounding a multiplication. Neat workaround. Thanks