Magic numbers or why "=(X/X - 1)" is not always equal to 0?

[flash-222]

I came across this strange result while calculating:

Can anyone explain this behavior:

- Why is the result of the vTest12 variable not 0?

- Why does the result of the variable vTest12 differ from vTest15?

- Why does the result of the variable vTest12 differ from vTest16 that has the same expression structure?

- Why does the result of the variable vTest13 differ from vTest14?

Initially, the problem arose because instead of the expected value of "0%" when formatting, we began to receive "-0.0%":

Of course we used a workaround, but I would like to understand the reasons. Any help is highly appreciated.

Qlik Sense (February 2024 Patch 4). 

[marcus_sommer]

It's mainly related to the used binary number-system which hasn't always an exact float-value else only an approximately one. The differences between the logically equal calculations are caused from splitting the calculation into several parts whereby the order of merging the n intermediate results are not mandatory equally and may depend on various factors.

The main-cause and possible workarounds are described here: Rounding Errors - Qlik Community - 1468808. 

[eddyvargas]

according to chatgpt when 7.4 is converted to binary is like 111.011001100110... periodic 0110 and is not exaclty

the solutions is Round($(i)/$(i),.00001)-1
or if($(i)/$(i)-1=0,0,$(i)/$(i)-1)

[flash-222]

Thank you, Eddyvargas. Your solution works, but it is not obvious from the start. It is a workaround after finding an error in calculations. But I would like the calculations to be predictable. I checked the calculation in several other systems and programming languages. Everywhere I got the result 0. So why does Qlik use such a calculation, which is not only different from other systems, but also not entirely logical for the user?
The problem is actually much more serious than it may seem at first. The formula "=X/Y - 1" is finding the deviation and is very often used in calculations. For example, you produce 10 parts, and your competitor 20 and you want to compare yourself with the competitor. The formula is as follows: 10/20 - 1 = -0.5 = -50%. This means that you produce 50% fewer parts than your competitor. You set the task for your analytics department to compare you with your competitor in all segments and report the deviation. Let's assume that you produce the same quantity in all segments as competitor, from 7.0 to 7.9:

As a result, your analytics department will determine that in half of the segments (50%) you are lagging behind your competitor (negative indicators). Although in fact you are on equal terms. Such a result.

[eddyvargas]

It's a bug, the condition If(7.4/7.4-1<0 is true

But if you use If(7.4/7.4-1>=0 is also true and correct for your analysis

[marcus_sommer]

The way how Qlik handles the matter is surely a restriction but IMO it's no mistake or even a bug. It's a design-decision from some decades before to avoid processing-overhead and not mandatory outdated nowadays just because the current hardware is so much more powerful. There are also other rather uncommon decisions like using a data-interpretaion instead of fixed data-types or the if-loop handling (calculating all conditions before the evaluation) which were all made in regard to the performance. And the processing-speed of Qlik is really great.

Nevertheless there is still a lot of room for improvement within the documentation to highlight the features with it's impact. Just mentioning the binary number-system without hinting clear to the rounding-challenges is not sufficient.

Whereby who starts nowadays really with the documentation - all tools could everything without any efforts - no conception or knowledge are needed else just using the preferred AI and we get a wonderful world ...

[flash-222]

Eddyvargas, you have found even more interesting case:

Thanks to everyone for your time. It turns out this is not a bug and all I have to do is start reviewing the calculations in over a hundred dashboards...