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?
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.
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.
Money() should just format the textual representation, but it will not touch the numerical part of the dual value.
So if you are concerned about accuracy of your calculations, different number formatting will not change anything.
Here is another blog post that explains the observed issues with rounding errors:
I hope the decimal places display for TD_Qty is just this, a formatted display, and that the value is stored internally as integer in the DB?
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
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.
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
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