topic Can someone explain this behavior...? in QlikView

Can someone explain this behavior...?

Wed, 17 Aug 2016 16:09:10 GMT

Hi Everyone

Can someone please explain why I get 243.59999999999.... when the value is 243.60? Clearly I am missing something but I can't for the life of me find an explanation.

Re: Can someone explain this behavior...?

jpenuliar — Wed, 17 Aug 2016 16:16:29 GMT

What version of Qlikview are you using?

Re: Can someone explain this behavior...?

kaushiknsolanki — Wed, 17 Aug 2016 17:14:21 GMT

Hi,

What I found is if you keep 14 digit decimal then it will show result the way you are getting, but if you reduce one decimal zero then it will show you correct result.

May be the issue is with the limit of the decimal numbers. Not sure though.

Regards,

Kaushik Solanki

Re: Can someone explain this behavior...?

marcus_sommer — Wed, 17 Aug 2016 17:15:03 GMT

Yes, see here: Rounding Errors.

- Marcus

Re: Can someone explain this behavior...?

Siva_Sankar — Thu, 18 Aug 2016 03:31:17 GMT

Joao,

Looks like bug or limitation of num function. I tried =num(243.60,'#,##0.0000000000000') and it works for me with (13 zeros)

Siva

Re: Can someone explain this behavior...?

Thu, 18 Aug 2016 08:38:49 GMT

Hi @Siva Sankar @Kaushik Solanki @Marcus Sommer

Thank you for you reply. I feel more inclined to agree with Siva and Kaushik I think there might be some limitation (bug) in the num function.

I read the rounding errors post by HIC but I don't think it applies. But I find this disturbing:

==================== Rouding Errors Post ====================

...This becomes obvious if you look at the hexadecimal representation of 0.01:

0.01 (decimal) = 0.028F5C28F5C28F…. (hex)

The sequence …28F5C… is repeated an infinite number of times, but since QlikView uses a finite number of binary digits (all according to the IEEE standard), QlikView will internally use a “rounded” number....

==================== Rouding Errors Post ====================

When I convert the same number 243.60 in SQL SERVER to DECIMAL I get as many zeros as possible. See example

SELECT CAST(243.60 AS DECIMAL(20,17))

Result : 243.60000000000000000

I would expect Qlikview to behave in the same way instead of returning 243.59999999999 which is completely inaccurate.

Re: Can someone explain this behavior...?

Siva_Sankar — Thu, 18 Aug 2016 09:38:13 GMT

Joao,

Either you may reach out qliktech support team so that they can raise it as a bug and fix it in next release or will give explanation if there is a limition of this function.

-Siva

Re: Can someone explain this behavior...?

Thu, 18 Aug 2016 09:56:52 GMT

Hi @siva sankar

I have logged a case with Qlikview Support I let you know how it goes.

Re: Can someone explain this behavior...?

marcus_sommer — Thu, 18 Aug 2016 10:03:11 GMT

For me it's not a bug else an intentionally designed behaviour - which had unfortunalely disadavantages - by using a relative simple method to process numbers (no data-types, limitating on max. 14 digits, no double-rounding or other overhead) to speed up the processing (and it worked). Each number-processing will have it's limitations and will be always a compromise between the accuracy and the needed CPU + RAM resources.

- Marcus

Re: Can someone explain this behavior...?

Thu, 18 Aug 2016 14:40:45 GMT

This is the reply from support:

QlikView uses IEEE 754 64-bit double-precision binary floating-point format to store all numbers. This means that 64 bits are used to store a

number: One bit for the sign, 11 bits for the exponent and 52 bits for the number itself. A 52 bit number corresponds to approximately 15 significant

decimal digits. This means that QlikView can store integers up to 14 digits while preserving the exactness of the number. However, integers with more than 14 digits will be rounded to 14 significant digits.

For non-integer numbers, the situation is slightly worse. Some fractional numbers can be stored in an exact form, e.g. 0.5 and 0.25, since

they have an exact binary counterpart. But others cannot, e.g. 0.1 and 0.2, since these do not have an exact binary counterpart. As a consequence, you very often have a “rounding error” in the 14th significant digit for non-integers...

These are not errors in QlikView. 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 you get rounding errors. There's no way around it...except for first converting the floating point number to an integer (by multiplication), leaving no decimal part, and thereafter rounding the integer accordingly and finally converting the integer back to a float (by division)…

Normally this kind of "rounding errors" are small and usually insignificant and should in most cases not influence the result of the analysis.