
Re: Numeric data format question
Chanty 4u Feb 26, 2016 9:48 PM (in response to Alexis Hadjisoteriou )Money(To_Price) as new Price

Re: Numeric data format question
Alexis Hadjisoteriou Feb 27, 2016 4:27 AM (in response to Chanty 4u)Thanks  I'll try and revert

Re: Numeric data format question
Stefan Wühl Feb 27, 2016 9:53 AM (in response to Chanty 4u)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?


Re: Numeric data format question
John Witherspoon Feb 26, 2016 10:15 PM (in response to Alexis Hadjisoteriou )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 Feb 27, 2016 4:26 AM (in response to John Witherspoon )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 Feb 27, 2016 11:05 AM (in response to 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 Feb 29, 2016 12:36 PM (in response to Alexis Hadjisoteriou )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 Mar 1, 2016 3:52 AM (in response to John Witherspoon )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 Mar 1, 2016 12:59 PM (in response to Alexis Hadjisoteriou )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 Mar 1, 2016 4:15 AM (in response to Alexis Hadjisoteriou )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

Re: Numeric data format question
Alexis Hadjisoteriou Mar 1, 2016 1:01 PM (in response to Jithu J)Hi Jithu
The question relates to the way that we store data internally and not how we display it
Thanks for responding though  much obliged
Alexis
