Behaviour of the Round function

[effinty2112]

Dear Qlikers,

A textbox with expression: Round(0.74/0.8,0.01) returns the value 0.93.

A textbox with expression: Round(0.74/(1-0.2),0.01) returns the value 0.92.

Both return 0.925 if the step is changed to 0.001.

This came to light when I replaced 0.8 in an expression with 1-$(vWastage) and had the value of vWastage set to 0.2  and couldn't get the same answers I got for the constant value.

Can anyone explain this behaviour so such discrepancies can be anticipated or avoided?

Kind regards

Andrew

[sunny_talwar]

I guess a good document to read on this topic is this -> Rounding Errors

[effinty2112]

Hi Sunny,

The thing I don't get is that in each case the Round function is receiving the same value for the first argument:

0.74/0.8 = 0.74/(1-0.2) = 0.925

I don't see why the Round() function gives different answers for the same argument.

If the Round() function gives an apparent error I'd expect it to be consistent for the same arguments.

Cheers

Andrew

[sunny_talwar]

I guess I understand the kind of consistency you are looking for, but from what you have tested, it seems that there is no consistency. I am not an expert on this, so I am hoping someone who might have done more research might be able to give a better response.

Best,

Sunny

[marcus_sommer]

As a workaround you could use something like this:

= round(0.74/$(=num(1-0.2, '#.##0', '.', ',')), 0.01)

whereby the num() is only necessary because my region-settings return a comma as decimal-delimiter which will led to an error within the $-sign expansion.

- Marcus

[johnw · Accepted solution]

The round function gives a different answer because you don't actually have the same function arguments.

Let me simplify a little, and just take the assertion that 1 - 0.2 = 0.8, and use a raw binary format.

You have three numbers involved here: 1, 0.2, and 0.8. A computer needs to store these numbers in some way before it can do anything mathematical with them. Well, in raw binary...

1   = 1
0.2 = 0.00110011001100110011... repeat forever
0.8 = 0.11001100110011001100... repeat forever

But we can't repeat forever, because we're on a computer with limited storage. So in my hypothetical binary format, I'm going to terminate it at only four decimal positions.

1       -> 1.0000
0.2     -> 0.0011 (3/16)
0.8     -> 0.1100 (3/4)
1 - 0.2 -> 0.1101 (1.0000 - 0.0011 = 0.1101, 1 - 3/16 = 13/16)

And so we see that a computer storing numbers in my binary format will not understand that 1 - 0.2 = 0.8. To the computer, they are slightly different.

QlikView is using IEEE 754 double precision binary floating point. The representation is not exactly like this. But it's subject to the same sorts of errors, and clearly has the same sort of error in this specific case.

It can be very difficult to avoid. However, integers are always reliably stored. So generally speaking, you'd want to keep all of your math in integer form. For accounting, this can be a matter of storing values in cents. But when you get into arbitrary decimal arithmetic, it can become impractical to impossible.

[johnw]

However, this is a rare case in which I disagree with Henric Cronström. Not on the facts about IEEE 754, or how you work around its limitations in QlikView. Just with his value judgment that these are not errors in QlikView, and his apparent assertion that our expectations of consistency are entirely unreasonable.

My value judgments differ. I consider these to be QlikView errors, and I don't blame anyone who expected the equals sign and round() to behave properly. I don't think his professor was threatening to flunk the people using QlikView, but rather to flunk the designers of QlikView, the people who chose to use IEEE 754 despite knowing that they needed to support an equals sign and a round() function and so many other things that users would rightly expect to behave consistently on decimal numbers.

That said, I also don't blame Qlik. They made a hard choice, but they probably made the best choice, the least of evils. Put me in the same situation with the same requirements and I'd probably have chosen IEEE 754 just like they did. I'd have first tested decimal floating point formats and calculation speed, and they probably did, but with no hardware acceleration and running on ancient computers, I'm sure I'd have quickly seen that no decimal floating point format was going to be practical, and I'd have reluctantly chosen a binary floating point format, knowing full well that QlikView would forever after be plagued by annoying little mathematical errors.

Well, probably. I've actually faced this decision before when designing a compiler for a simple language with a single numeric format. I actually went the other direction. I did my initial testing with binary floating point, but the errors drove me mad. I ultimately went with a decimal format, and have lived with the performance problems. I think I made the right choice. For me, better the right answer slowly than the wrong answer quickly. But I needed accounting rigor. QlikView is where you do your reporting, not, I hope, where you balance your books or pay your taxes. Never do your accounting in binary.

Decimal has the same sort of errors, mind you. Any computer representation of numbers as digits in some base does. Try doing this on a computer using decimal: 1/3 + 1/3 = 2/3. It won't be true. Decimal cannot accurately store these numbers any more than binary can accurately store 0.8 and 0.2. However, we can expect QlikView to much more often be dealing with accounting sorts of numbers, like values in US dollars, say. A decimal format could store those accurately. A binary format cannot.

Heh. Sorry if that was TMI, and I hope it wasn't overly confrontational. I can get passionate about math and numbers.

[Peter_Cammaert]

Good to see you back up to speed again, John. Currently thinking about crowns.

One remark from a math nitwit: I think your example (1/3+1/3) proves that the problem actually has nothing to do per se with computers but with math in general. Didn't someone invent rational numbers to cope with that sort of errors in a controlled fashion. There is however no corresponding hardware representation in general purpose computers, so we have to deal with it in software (numerator/denominator). Unless it endangers performance and that's the ultimate compromise in QlikView. What compromise would you chose?

[johnw]

I'll at least agree that it's a general problem with terminating digit representations of numbers, which arguably has nothing to do with computers per se.

But generally in math, 1/3 would be represented as 1/3, π would be represented as π, and we wouldn't be bothered by the non-terminating decimal nature of the first, or the non-terminating non-repeating decimal nature of the second.

Well, I guess it can bother us in applied math. But in applied math, a reasonable approximation is fine. If we're building a bridge, and we need to use π, a handful of digits might be plenty.

"Invent" rational numbers?! I'm a Mathematical Platonist! I believe we discover mathematics, not invent it! It also seems likely that the concept of fractions predates history, with simple concepts like, "I'll give you half of what I have". But certainly we can do more with rational numbers than we can with, say, positive integers.

[Peter_Cammaert]

Rational number are a human solution for a problem that pure math isn't even aware of. It's a construct able to solve problems. Okay, now I'm shooting myself in the foot. Stil thinking about crowns, I guess.

On the other hand, we stil have this compromise that Qlik has selected. Would QlikView/Sense be built in a different way now than when it was in the early 90s? The enviroment has changed (a lot) but the essential core cpu properties haven't.

I don't think so...