11 Replies Latest reply: Mar 1, 2016 1:01 PM by Alexis Hadjisoteriou RSS

    Numeric data format question

    Alexis Hadjisoteriou

      Hi

      I am importing data from Navision and I am not happy with the way that the numeric data looks:

      All the numeric values are monetary values and should have 2 decimal places (i.e. 0,15 and not 0,14999999999999900) as per example below

      I am not sure what I need to do to achieve this during loadup. Can you assist?

      Paps.jpg

        • Re: Numeric data format question
          Chanty 4u

          Money(To_Price) as new  Price

          • Re: Numeric data format question
            John Witherspoon

            Since numeric data in QlikView is stored as binary floating point, it is impossible to represent some fractions with complete accuracy, just like it is impossible to represent 1/3 in decimal with complete accuracy. I suspect that's your issue here.

             

            I've so far never felt the need to address the problem in any of my applications. I just format with two digits after the decimal and call it good.

             

            If you must have exact accuracy in your calculations to, say, two digits after the decimal, you could multiply the raw data by 100 before it ever gets stored as a number in QlikView. Keep all such numbers like that, 100 times as big as they should be, then only divide by 100 as the very last step in whatever calculation you're doing when it comes time to display the data.

             

            One possibility, anyway.

              • Re: Numeric data format question
                Alexis Hadjisoteriou

                Thanks John,

                Most ERP systems that I access to pull data out deliver the data as I expect it in 2 decimal points - for some reason in this instance Navision seems to be delivering it to 20 dp. My first concern is the space that this might be consuming as I appear to be hitting memory issues with this application and looking at possible causes. I'll convert to Money() as was suggested by someone else if you think this is more compact than at present.

                 

                thanks again

                Alexis

                  • Re: Numeric data format question
                    Alexis Hadjisoteriou

                    Can I also add John that these are not fractions. They are supermarket transactions with 2dp values. For some reason Navision stores 0.15 as 0.1499999999 which is a nuisance

                      • Re: Numeric data format question
                        John Witherspoon

                        I understand, but 0.15 is 3/20. Now what happens if we try to represent that in binary floating point? If I calculated correctly, that's .0010011001100110011001100110011... and so on forever. QlikView of course doesn't have infinite bits to store this in. So at some point (I believe after 51 significant bits), it stops repeating the 0011 pattern. That means that it doesn't QUITE come to 0.15. It comes to 0.14999999... just like you observe. It is simply impossible to store the number "exactly 0.15" in binary floating point, just as it is impossible to store the number "exactly 1/3" as a decimal floating point. These are just fundamental limitations of representing fractions with a finite number of digits, regardless of base.

                         

                        However, even if we can't represent "exactly 0.15" in binary floating point, we CAN represent "exactly 15" in binary floating point. It's just 1111. So that was my suggestion if you truly need exact values rather than a binary floating point approximation of your values - multiply by 100 before storing the data, then divide by 100 as you display the data. All calculations will then be exact right up until the time of display.

                         

                        Give the previously linked explanation of "Rounding Errors" a read if it's still confusing to you. Heck, give it a read regardless. In it, hic likewise suggests multiplying by 100 as a solution IF exact values are required. So rather than storing dollars, for instance, store cents.

                          • Re: Numeric data format question
                            Alexis Hadjisoteriou

                            Thank for your detailed and very helpful explanation John - much appreciated.

                            What I failed to explain is that the values that we are handling are NOT originating from fractions - they are ticket values on items that you and I buy from a supermarket - they are not results from calculations or ratios or anything else that could be a fraction - they are all in euros and cents in the format €n.nn - the representation that you have seen in my screenshot at the top of this message shows how Navision returns these values which in my opinion is a glitch/error rather than a feature.

                             

                            regards and thanks again

                            Alexis

                              • Re: Numeric data format question
                                John Witherspoon

                                Yes I understand that your numbers are money. I'm sorry I said "fraction" as part of my explanation as you seem to have locked onto that and decided I'm not understanding or explaining your problem. I understand your problem. I am explaining your problem. I am telling you that it is impossible to store 0.15 as a binary number, and explaining why, and explaining how you can solve the problem if it matters enough to you. I am not trying to tell you that .149999999 is a feature. I am telling you that it is the inevitable result of choosing base 2 to represent base 10 decimals.

                                 

                                You can argue that it is a glitch or error. I would actually agree with you, even if hic disagrees in his blog post. But I believe I understand why it not only WON'T be fixed, but SHOULDN'T be fixed. To fix it, QlikView would have to use a decimal floating point. For the purposes of QlikView, speed is probably more important to most people in most situations than the accuracy you're wanting, and I suspect that most hardware processes binary floating point much faster than decimal floating point.

                                 

                                So we live with it. You're probably one of the few people who even noticed it - congratulations! But QlikView is a BI system, not an accounting system. We're usually trying to understand the big picture, not balance to ten decimal places and satisfy our auditors and the government. If you NEED your two decimal place numbers to be EXACT, then store your numbers in cents instead of Euros (multiply by 100 before storing the data in QlikView), and you'll have an exact representation. It's a mild pain, but that's what you'd need to do. But I'm willing to bet you don't actually need to do it. Probably all you need to do is stop displaying the number that is actually being stored, and just format it with two decimal places like your users will want to see. You'll then see the number you expect to see.

                                 

                                Actually, I'm unclear if the problem is in Navision or QlikView in your specific case, but the explanation is the same. It's just worth mentioning because if the problem already exists in the exported data, multiplying by 100 won't fix it. But again, it probably doesn't need to be fixed.

                                 

                                Please read the blog post swuehl linked to, Rounding Errors, if there's any remaining confusion.

                      • Re: Numeric data format question
                        Jithu J

                        Pretty simple... Just after U created ur measure ... just go to visualization option then Measures -> Number Formatting -> Number then choose 1,000.02 ... then you will get to 2 decimals