Rounded numerical values sometimes get unexpected results

[ToniKautto]

This article shows two kinds of issues where rounded numerical values sometimes get unexpected results.

1) Example for using rounding of floating point numbers with round() function:

Load decimal value data into QlikView like for example:

LOAD * Inline  [
F1
3.975
4.975
];

The values do not get rounded as expected when displayed with two decimals in a sheet object:



Result:

The value 4.975 is rounded to 4.97 instead of to the expected value 4.98.
This occurs when a value is rounded by either sheet object’s Number Format Setting or Round() function.

2) Example for using rounding of floating point numbers with floor() function:

For the value 3.1 we would like to define different formats whit different decimal places from 1 to 6 with the floor() function:

Result:

The values in the table above do not get rounded as expected for 4 and 5 decimal places for 3.19 with the floor() function, instead you get the values 3.1899 and 3.18999.

Environments:

• QlikView (all versions)
• Qlik Sense (all versions)

This behavior works as designed.

QlikView (and Qlik Sense) 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 different. 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. 

These are neither errors in QlikView and nor 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 (or truncating) the integer accordingly and finally converting the integer back to a float (by division).

Related article about Floating point value precision in QlikView: Decimal Values in Qlik Sense and QlikView

Resolution:

Workaround for rounding issues mentioned in 1):

Load all numerical data as integers, by raising the decimal value to a suitable power of 10. Remove the decimal part by flooring the result.

LOAD 
    Floor(F1*1000) as F1
Inline [
F1
3.975
4.975
];

The decimal value can be restored by dividing and rounding.



Notice that the division and rounding needs to be applied on the aggregated result, so that the aggregation is performed on integer values only.


Workaround for rounding issues mentioned in 2):


The workaround for the rounding issues for the second case is similar. You convert 3.19 to an integer by multiplying with 100 within the floor function and divide it by 100 (please see screenshot above).

Finally you go to the "Chart Properties" --> "Number" --> "Fixed to" and define the desired decimal places (please see screenshot below):