Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

 

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

39 Comments
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
3,923 Views
charlotte_qvw
Partner - Creator
Partner - Creator

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
3,923 Views
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

3,922 Views
charlotte_qvw
Partner - Creator
Partner - Creator

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

0 Likes
3,922 Views
edzard_c2bc
Partner - Contributor
Partner - 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
3,922 Views
hic
Former Employee
Former 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
3,922 Views
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

Thanks HIC.

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

0 Likes
3,922 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?

3,922 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
3,804 Views
marcus_sommer

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
3,804 Views