Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alexis
Partner - Specialist
Partner - Specialist

Numeric data format question

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

11 Replies
Chanty4u
MVP
MVP

Money(To_Price) as new  Price

johnw
Champion III
Champion III

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.

alexis
Partner - Specialist
Partner - Specialist
Author

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

alexis
Partner - Specialist
Partner - Specialist
Author

Thanks - I'll try and revert

swuehl
MVP
MVP

Money() should just format the textual representation, but it will not touch the numerical part of the dual value.

Data Types in QlikView

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:

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?

alexis
Partner - Specialist
Partner - Specialist
Author

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

johnw
Champion III
Champion III

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.

alexis
Partner - Specialist
Partner - Specialist
Author

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

Not applicable

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