topic Re: Behaviour of the Round function in QlikView

Behaviour of the Round function

effinty2112 — Tue, 25 Oct 2016 15:48:17 GMT

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

Re: Behaviour of the Round function

sunny_talwar — Tue, 25 Oct 2016 15:50:35 GMT

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

Re: Behaviour of the Round function

effinty2112 — Tue, 25 Oct 2016 16:10:24 GMT

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

Re: Behaviour of the Round function

sunny_talwar — Tue, 25 Oct 2016 16:20:12 GMT

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

Re: Behaviour of the Round function

marcus_sommer — Tue, 25 Oct 2016 19:05:48 GMT

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

Re: Behaviour of the Round function

johnw — Tue, 25 Oct 2016 19:59:09 GMT

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.

Re: Behaviour of the Round function

johnw — Tue, 25 Oct 2016 21:08:41 GMT

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.

Re: Behaviour of the Round function

Peter_Cammaert — Tue, 25 Oct 2016 22:31:45 GMT

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?

Re: Behaviour of the Round function

johnw — Tue, 25 Oct 2016 23:07:17 GMT

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.

Re: Behaviour of the Round function

Peter_Cammaert — Tue, 25 Oct 2016 23:43:36 GMT

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...

Re: Behaviour of the Round function

johnw — Wed, 26 Oct 2016 00:00:29 GMT

Yes, I suspect that even if Qlik was writing from scratch starting today, they'd select the same numeric format. It doesn't seem anything fundamental has changed. Yes, computers are much faster. We also expect that much more out of them.

Someone offers you a choice - "I can fix most of the cases where you see mathematical inconsistencies in Qlikview. However, your charts will take five times (?) as long to render."

Do you take the offer? I wouldn't where I work. I'll keep the speed. Other companies might decide differently.

Re: Behaviour of the Round function

Peter_Cammaert — Wed, 26 Oct 2016 00:22:25 GMT

Okay, good! Now you're pointing my own gun onto myself.

I wouldn't select any other behavior or outcome, as long as it's predictable, it offers some sort of escape, and there is someone available to explain the reasoning behind all of this (instead of having to spend hours, even days figuring out what exactly is going on)

The stuff I'm being harassed confronted with the last couple of years (SAP) already provides solutions where rounding errors are critical (numerator/denominator). I do get some support from the data source, which makes life a bit easier. But I can imagine that this may not be the case for everyone. They now have something to read, before deciding on the best approach.

Re: Behaviour of the Round function

effinty2112 — Wed, 26 Oct 2016 08:10:44 GMT

Hi John,

Thank you very much for your explanation - very clear now how this comes about. I had a tiny inkling about where the fly in the soup came from and you've shown me.

For the problem at hand I think a doubly rounded expression:

=Sum(Rate*Round([Unit SQM]/Round((1-$(vWastage)),0.000001),0.01))

(Rate in £Sterling, [Unit SQM] to four decimal places)

will do the job for me here as the vWastage variable will never be to more than three decimal places and, probably more importantly, the final result will never be to more than five significant figures and it tests okay compared to the equivalent expression with divisor written in as a constant.

Thanks again to you and to everyone else who commented.

Kind regards

Andrew