Class() function with floating point value

Mukhiddin — Tue, 10 May 2022 19:01:03 GMT

When floating point numbers are used in the parameter Class() function expected result cannot be achieved. For example, expected output of class(0.15, 0.05) is "0.15 <= x < 0.2", however output is "0.1 <= x < 0.15" which 0.15 is not included in interval.

Please refer to following to learn about more class function.

class - script and chart function

Environment

QlikView, any version
Qlik Sense, any version

Resolution

It is expected behavior.

Workaround

To get desired result, first convert the floating point number to an integer (by multiplication), leaving no decimal part, and thereafter round the integer accordingly and finally convert the integer back to a float (by division).

Please check following script in the example of class(0.15, 0.05) to get desired "0.15 <= x < 0.2" result:

SET DecimalSep='.';T1: Load *, dual(String, First) as DualClass; Load *, First&' <= x < '&Second as String; Load *, subfield(Class100,' ',1)/100 as First, subfield(Class100,' ',5)/100 as Second; Load *, class(Value,'0.05') as Class, class(Value*100,'0.05'*100) as Class100; Load recno() as ID, recno()/100 as Value autogenerate 20;

Cause

This is only a problem when you use a single constant floating point value. When you have a base 10 system (like ours), it can only express fractions that use a prime factor of the base. The prime factors of 10 are 2 and 5. So 1/2, 1/4, 1/5, 1/8, and 1/10 can all be expressed cleanly because the denominators all use prime factors of 10. In contrast, 1/3, 1/6, and 1/7 are all repeating decimals because their denominators use a prime factor of 3 or 7. In binary (or base 2), the only prime factor is 2. So you can only express fractions cleanly which only contain 2 as a prime factor. In binary, 1/2, 1/4, 1/8 would all be expressed cleanly as decimals. While, 1/5 or 1/10 would be repeating decimals. So 0.1 and 0.2 (1/10 and 1/5) while clean decimals in a base 10 system, are repeating decimals in the base 2 system the computer is operating in. When you do math on these repeating decimals, you end up with leftovers which carry over when you convert the computer's base 2 (binary) number into a more human readable base 10 number.

These are not errors in QlikView or Qlik Sense. And they are not errors in IEEE 754 either. Rather, they represent errors in the expectation of binary floating point numbers. It's simply that some values cannot be exactly represented as binary numbers, so result gets rounding errors.

article Class() function with floating point value in Official Support Articles

Class() function with floating point value

Environment

Resolution

Workaround

Cause