Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Working with decimal values in QlikView can lead to unexpected results like incorrectly rounded values or incorrect aggregated results. This session will explain the reasons behind the most common issues related to decimal values in QlikView, and suggested techniques on how to avoid the issues.
31.October 2013
For best viewing, we recommend you set the highest video resolution.
NOTE: Unfortunately it's not that easy to read the numbers in the QlikView examples and we are sorry for the inconvenience. For that reason, we added the QWV files for your reference.
You can download the QWV Examples in the attachments - Example QVW.zip
Q&A - Troubleshooting unexpected numerical values in QlikView
Q: What exact floating point data type is used in QlikView?
A: QlikView stores floating point values according to IEEE754 standard.
754-2008 - IEEE Standard for Floating-Point Arithmetic;
http://ieeexplore.ieee.org/xpl/login.jsp?tp=&arnumber=4610935&url=http%3A%2F%2Fieeexplore.ieee.org%2...
Q: My source data has 18 digit values, 16 digits integers and 2 decimals. How can this data be loaded into QlikView?
A:It is not possible to store values of that size and precision in QlikView. Decimal values cannot be larger than the floating point limitation of 17 digits (64 bit software). In addition also consider the legacy limitation on 14 digits, which means that you cannot store larger integer values than 14 digits.
Consider loading the values as a presentation of thousands, millions or billions.
Q: Values with an E are interpreted as scientific notation. Can this be avoided?
A: This is according to QlikView standard associative behavior. Values with number and one E will be interpreted as a scientific notation. Use Text() during load to store the value as text, without numerical interpretation.
Q: Is it advisable to always format numerical values during load?
A: There is no need to format data during load if you know that the incoming values match your requirements and expectations. It can be a good idea to limit loaded values decimal precision to the applications presentation, so that aggregations makes sense visually as seen in the session example of aggregation related issues.
Q: Can Round() function be used when comparing decimal values to get the expected comparison result?
A: No. Round() returns a floating point value, and therefore rounded values can always vary on the stored decimal digit.
Q: Data is loaded from database with "Force 32 Bit" enabled. Will the stored values get 14 digit mantissa?
A: No. All decimal values in 64 bit QlikView have 17 digit mantissa.
Q: There is different information about QlikView and dual values. Some sources say that every value has both components, while other sources say that only dual values have both. Can you confirm please?
A: It is not relevant how the values are stored technically. The important thing is that all values should be considered as dual values.
Q: Does the text representation of numerical values affect calculations?
A: No. All calculations are done on the underlying numerical value.
Q: What is the precision of Dates and TimeStamps? Could floating point values explain why two apparently similar times are different when used in calculations?
A: Pure date values should have an underlying integer representation. Timestamps are represented by floating point values. Use the Num() function to evaluate the underlying values, and thereby validate that the calculated results are accurate.
Q: So is it always a wise move working the numbers on the backend than on Expressions?
A: Pre-calculations or formats during load is often wise, since it increases performance and gives an easier data model to work with in the front-end.
Q: Is there something like Java's BigDecimal or BigInteger?
A: No.
Q: Is it required to use functions like Num() or Round() for every load?
A: The format needs depend on the incoming data and the application front end. This needs to be evaluated on a case by case basis.
Q: What is the difference between Num() and Num#() function?
A: Both functions return a dual value.
Num() takes a number as input, and stores this as the numerical part of the dual value. The text part will be formatted according to the document format variables, or the additional parameters in the Num() functions.
Num#() take a text string as input, and stores it as the text part of the dual value. The text is interpreted as a number, based on the document format variables or according to the additional parameters in the Num#() functions.
For a full reformat it can sometimes be needed to nestle the two functions like Num(Num#())
Q: Can comparison be made by subtracting values and then evaluating the delta result?
A: It is never possible to determine the precision of the last digit in floating point values. The best way to compare decimal values is generally to convert the decimal values to integers, and then compare the integers.
Keep in mind that the below example will for example the integer strategy would probably not be as expected;
Floor(0.15 * 100) = Floor(0.1499999999 * 100)
