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

[johnw]

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.

[Peter_Cammaert]

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.

P.

[effinty2112]

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