Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Rounding Errors

If you use equality as a condition when comparing floats, I will flunk you!

I can still hear the words of the Professor in my first programming class when studying for my engineering degree. The threat was very real – he meant it – and the reason was of course the fact that you cannot (always) represent decimal numbers in an exact binary form.

For example, we would never dream of writing a condition

  If( x = 0.3333333 , … )

when we want to test if x equals a third. Never. Because we know that a third cannot be represented exactly as a decimal number. No matter how many threes we add to the number, it will still not be exact.

But it is not uncommon that people make comparisons with an exact decimal number, similar to

      If( x = 0.01 , … )

thinking that it is a valid comparison, although it leads to exactly the same problem as the previous comparison! This becomes obvious if you look at the hexadecimal representation of 0.01:

      0.01 (decimal) = 0.028F5C28F5C28F…. (hex)

The sequence …28F5C… is repeated an infinite number of times, but since QlikView uses a finite number of binary digits (all according to the IEEE standard), QlikView will internally use a “rounded” number.

So what are the consequences? Well, QlikView will sometimes deliver the “wrong” number as result. Examples:

      Ceil( 0.15, 0.01 ) will return 0.16

      Floor( 0.34, 0.01 ) will return 0.33

      0.175*1000 = 175 will return FALSE

      Time( Floor( Time#( '04:00:00' ),1/24/60/60 )) will return 03:59:59

What you see are not errors in QlikView. And they are not errors in IEEE 754. Rather, they represent errors in the expectation and usage of binary floating point numbers. Once you understand what binary floating point numbers really are, it makes perfect sense. It's simply that some values cannot be exactly represented as binary numbers, so you get rounding errors. There's no way around it.

Should you want to investigate this yourself, I suggest you start with the following script that generates 100 numbers and their rounded counterparts. In five cases the Ceil() function rounds "incorrectly" and generates a "Diff" different from zero:

Load

   Num(Rounded,'(HEX) 0.000000000000000','.',' ') as RoundedHEX,

   (Round(100*Rounded) - PartsPer100)/100 as Diff,

   *;

Load

   Ceil(PartsPer100/100, 0.01) as Rounded,

   *;

Load
   RecNo() as PartsPer100

   Autogenerate 100 ;

So, what should you do?

First of all, you should realize that the rounding errors are small and usually insignificant. In most cases they will not affect the result of the analysis.

Further, you could avoid rounding with Floor() and Ceil() to sub-integer fractions.

Also, you could convert the numbers to integers, because the errors will only appear if the numbers can have sub-integer components. For instance, if you know that you always deal with dollars and cents, you could convert the numbers to (integer) cents:

  Round( 100*Amount ) as Cents

Or if you know that you never deal with time units smaller than seconds:

  Round( 24*60*60*Time#( Time, 'hh:mm:ss' ) ) as Seconds

And finally, you should never use equality as a condition when comparing floats. Use greater than or less than. My professor isn’t here to flunk you, but rest assured: In his absence, QlikView will do it for him.

HIC

37 Comments
gupta_n8
Valued Contributor II

Good one

0 Likes
471 Views
dmohanty
Valued Contributor

Surprised to know...It made a good learning!

Thanks for sharing.

0 Likes
471 Views

Very useful post. I favor using date/time functions (for time values) to avoid the rounding issue. For example, to get Seconds in your last example I would use:

Interval( Time#( Time, 'hh:mm:ss' ),'s' ) as Seconds


It just seems more "documented" to me. See also

Qlikview Notes: Correct Time Arithmetic


-Rob

471 Views
Employee
Employee

The interval function will round the display and in Rob's formula it will indeed display only integer seconds. But it will not round the numeric part of the dual. So you may still get some rounding issues when comparing this number with other timestamps.

HIC

471 Views
carbal1952
Contributor II

I'm not surprised. Qlikview is a Pandora's box.

Good for you, Henric.

CB.

0 Likes
471 Views
Luminary
Luminary

Nice post HIC.

0 Likes
471 Views
Not applicable

your post is very hot.

0 Likes
471 Views
Employee
Employee

Interesting part of Numerical Analysis which is very often forgotten...

As Henric points out, "you should realize that the rounding errors are small and usually insignificant. In most cases they will not affect the result of the analysis."

Nevertheless, if you plan to rely on comparing values to compute some flags in your script, you could end up with unexpected results.

In my "finance-software-developer" past, we used to compare doubles or floats with an "epsilon" (0.0000000001 for instance):

if (xabs(myFloat1 - myFloat2) < 0.0000000001) then

     // Equals

else

     // Not Equals

endif

This would probably work in QlikView as well!

0 Likes
471 Views
Employee
Employee

The point that Brice makes is very good. The result will be that small differences will be neglected. In QlikView you could define the following variable:

     Set vRoundingEpsilon = 0.0000000001 ;

then the following expressions would make sense:

     If(fabs(myFloat1-myFloat2) < $(#vRoundingEpsilon), <Equals>, <Not Equals> )

     Floor(myFloat1 + $(#vRoundingEpsilon),0.01)

     Ceil(myFloat1 - $(#vRoundingEpsilon),0.01)

However, you must realize that small differences will be neglected also in the odd cases when you don't want them to. But they are probably so scarce, that it is acceptable.

HIC

0 Likes
471 Views
kalyandg
Contributor II

hi HIC,

really super post

Best Regards,

Kalyan

0 Likes
471 Views
Not applicable

Good post HIC. The other thing I have noticed is that without using rounding of floating point numbers, you can end up with issues when using interval matches in Qlikview too.

0 Likes
471 Views
charlotte_qvw
Contributor

Hi, I have a seconds rounding problem.I'm struggling to display 152.76 seconds as 2min 33 secs. I always get 2min 32 secs as QlikView doesn't round up the .76 sec. This is my code -

t1:

load * inline [

seconds

152.76];

t2:

LOAD

  Interval(seconds/86400, 'hh:mm:ss') as [Calls Handling Time]

Resident t1;

DROP Table t1;

Maybe I have to live with it as a rounding error but would be interested in any thoughts. thanks.

0 Likes
471 Views

Charlotte Beattie

Apply a ceil or round function to the seconds

Interval(ceil(seconds)/86400, 'hh:mm:ss') as [Calls Handling Time]

I'd also like to suggest that you avoid using division to convert time units and instead use QV functions. See my link to a blog post earlier in this thread. I would code your conversion as:

Interval(interval#(ceil(seconds),'s'), 'hh:mm:ss') as [Calls Handling Time]

-Rob

471 Views
charlotte_qvw
Contributor

Thanks Rob, still working it all through; that has set me on the right track

0 Likes
471 Views
edzard_c2bc
New Contributor

Thanks this is very insightful. Is there a way of creating my own QlikView functions that that I can use instead of or in parallel with something like ‘round’ or 'num'?

= round(num(298.2751,''),.01) &'
' &           
round(num(298.2750,''),.01) &'
' &           
num('298.2751','# ##0.00') &'
' &           
num('298.2750','# ##0.00')

0 Likes
471 Views
Employee
Employee

There is no magical way to get it right. Our minds are taught to think in decimal terms but the number representation in computers is binary...  The closest way is to add (or subtract) a small amount before rounding. See my previous comment on this.

HIC

0 Likes
471 Views
joaquinlr
Valued Contributor II

Thanks HIC.

I passed by binary rounded magic some months ago ... as you said we think in decimal terms!!!

0 Likes
471 Views
Not applicable

HC, as much as I appreciate that Qlikview uses floating point numbers and that we think in decimals and computers binary, it does not detract from the fact that people expect that figures should follow "expected" rounding principles. I do realize there are many rounding methods based on industry i.e. bankers rounding etc, other technology platforms provide the user the ability to select which they would like to use. Qlikview offers more functions than I have ever seen in any other technology platform, surely providing the user the option to select a rounding method and therefore controlling their preferred method is the easiest and simplest solution?

We are finding that when Qlikview's values do not balance to our line-of -business-reports values auditors immediately question our systems integrity. When trying to explain to them that its a Qlikview issue because they use floating points that comply with IEEE 754 etc, they simply and frankly don't care, we end up looking like fools...... Your statement that rounding errors are small and insignificant is simply not accurate at all when it comes to certain industries, Qlikview is a BI toolset that calculates and aggregates data and when finance and auditing teams naturally compare Qlikview results to business reports to determine accuracy, they expect that the 2 should balance - being 1c out is simply not acceptable.

Lastly, providing a workaround by adding a small number to the result is complex and messy and it still seems like the results are not guaranteed. As mentioned earlier, providing a couple more functions for the user to control rounding would put this issue to rest, this is surely something that is very easy for you guys to do given the other much more complex functions Qlikview offers?

471 Views
Not applicable

I recently was facing this problem with roundings too and the customer absolutely wanted the results of the roundings to be correct.

The workaround I found out was to treat the number as a string, in this way qlikview interprets it correctly.

Let's see an example:

round(33.15,.1) will return 33.1 (wrong!)

The round function infact looks to work on the internal representation of the number: if I write in a text object num(33.15,'0,000000000000000') I get as a result 33.149999999999999 that rounded gives exactly 33.1

On the contrary if I treat the number as a string the result will be correct: subfield( ( 33.15 + 0.05 ) * 10, ',', 1 ) / 10 will return 33.2 (exact!)

Note that floor( ( 33.15 + 0.05 ) * 10 ) / 10 will return again the wrong result 33.1

This workaround works only with positive numbers, if you need to work also with negative numbers you have to treat the sign, that will make the code even more heavier. Clearly I was not very happy to write that piece of code everywhere in the frontend to get a correct round... but at least it worked.

In my opinion if I can get the correct result with a workaround I would expect to get it also from the round/ceil/floor functions too!

0 Likes
471 Views

Unfortunately these article - Wie Computer Zahlen speichern -c't-Archiv,23/2014,Seite 172 - is commercial and in german but it explained quite well why the processing from numbers must lead to more or less rounding errors depending on the datatypes which are be used.

- Marcus

0 Likes
471 Views
hugmarcel
Contributor III

Hi

I am using round(value,maketime(0,0,1)), to circumvent some time calculating problems, as stated in

http://community.qlik.com/thread/58513

- Marcel

0 Likes
471 Views
Luminary
Luminary

OK. Can someone please explain this?

Round((0.65)*100, 10)//=70

Round((0.35+0.3)*100, 10)// =60

Round(0.65, 0.1)//=0.7

Round(0.35+0.3, 0.1)// =0.6

0 Likes
471 Views
pljsoftware
Contributor III

Hello Juraj,

I think the problem is binary rappresentation of 0.3.

To test it use the num(0.3, '(bin)').

Best regards

Luca Jonathan Panetta

0 Likes
471 Views

It's quite clear 0.35 + 0.3 < 0.65 because of the rounding errors or respectively rounding blur - probably something like 0.64999999999999999?. In my above mentioned link it will be very clear explained why computers with which type of internal number-processing must be differ from a pure mathematically calculation.

I think if you checked: if(0.65 = 0.35 + 0.3, true(), false()) you will get a false.

- Marcus

0 Likes
471 Views
jonascbi
New Contributor III

num((0.30+0.35)*100,'0.000000000000000')

displays the output

64.999999999999986

0 Likes
471 Views
Not applicable

Good one.

0 Likes
471 Views
MVP
MVP

I know I'm two years late to this, and as a non-employee, perhaps I shouldn't answer questions posed to Qlik / Henric, but...

"...other technology platforms..."

May have similar problems. Try this in Excel:

=ceiling(100.01-100.00,.01)

The right answer is .01. Excel returns .02.

"...surely providing the user the option to select a rounding method and therefore controlling their preferred method is the easiest and simplest solution?"

I fully agree that QlikView should allow us to specify the rounding method, and that doing so should be easy and simple.

But the errors you're seeing are not a problem with the round() function itself. It's a property of the internal representation of numbers in QlikView. Qlik can't make the round() function work properly on decimal numbers without also changing the internal representation of numbers to use base 10 instead of base 2.

That's not easy. That's not simple. And it would probably also have dramatic consequences for performance, based on the assumption that most servers process double precision binary floating point calculations much faster than decimal floating point calculations (with a floating point unit, for instance). If I were in charge at Qlik when they decided to use IEEE 754, I'd have probably made the same decision.

And even if QlikView did change to a decimal format, the round() function would technically still not be correct. It would return correct results on decimal numbers, yes. But it still couldn't handle base 3 numbers, for instance. Nobody's auditing financial statements in base 3, though, so it's admittedly much less of a practical problem.

"When trying to explain to [auditors] that its a Qlikview issue because they use floating points that comply with IEEE 754 etc, they simply and frankly don't care... they expect that the 2 should balance - being 1c out is simply not acceptable."

Agreed. Absolutely. Completely. If I were an auditor, and someone tried to use "we're using IEEE 754" as an excuse, I would come down on them HARD. "You're doing WHAT?! Are you KIDDING me? You can't do your financials in IEEE 754!!!"

So what DO you do?

I think the RIGHT thing to do is to handle your financial reporting in a system that uses a decimal representation, i.e., not QlikView. (Sorry, Qlik. I love y'all. I love QlikView. It's ubiquitous in our company. It even has a lot of our financial data in it. But that freaks me out. Double when an auditor asks me for information.)

A fairly PRACTICAL thing to do, if you're already using QlikView for auditable financial reporting and will continue doing so, is to convert all numbers to integers before loading them into QlikView. Binary floating point, and thus QlikView functions like round(), have 100% accuracy on integer values. (We've not done this. We're still storing in dollars and hoping for the best.)

And you may be able to calm down your aggravated auditor. For instance, maybe I just yelled at you as indicated above. Tell me firmly, "We are NOT doing our financials in IEEE 754. All the REAL financial processing is handled outside of QlikView. It's all done in Financial Tool XYZ, which uses decimal. THAT is what we use for our books. THAT is how we handle our taxes. QlikView is not our system of record. In regards to the financials you see in it, we are using it ONLY as a decision support tool. We do a billion dollars of business a year. We don't need cent or even dollar accuracy within our decision support tool in order to make the right decisions. We only need complete accuracy for our underlying financials, and we HAVE it for our underlying financials. So calm down."

"Lastly, providing a workaround by adding a small number to the result is complex and messy and it still seems like the results are not guaranteed."

The guaranteed fix is converting all numbers to integers before loading them into QlikView, and using only integer values in QlikView.

"...providing a couple more functions for the user to control rounding would put this issue to rest, this is surely something that is very easy for you guys to do..."

I fully support optional specification of rounding methods. It should be very easy to do that. But it won't put this issue to rest, because it won't fix the actual error. The round() function isn't where the problem lies.

471 Views
ecolomer
Honored Contributor II

Very nice and clear post

Thnak's for sharing

Saludos

Enrique

0 Likes
471 Views
beck_bakytbek
Valued Contributor III

Thanks for your sharing,

as always very informative,

Henric, i have one question, namely if create a gauge: i can Show a right number with right rounding (for instance: 94,45 % ), but on the surface: i mean : Text as Pop up, i see always 95 %.

how can i fix it this Deviation?

thanks a lot in advance

beck

0 Likes
471 Views
Employee
Employee

Usually you can do this and similar things by using two measures, with the same formula in both. Only the first one is used for the gauge.

Below I have different number of decimals in the two measures, and only the second one is active as pop-up. (You may need to remove the "Pop-up labels" check mark on the Presentation tab, also.)

Image1.png

Image2.png

471 Views