Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Henric_Cronström

 

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
gupta_n8
Specialist II
Specialist II

Good one

0 Likes
4,963 Views
dmohanty
Partner
Partner

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

Thanks for sharing.

0 Likes
4,963 Views
rwunderlich
MVP & Luminary
MVP & Luminary

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

4,963 Views
Henric_Cronström

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

4,963 Views
carbal1952
Creator II
Creator II

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

Good for you, Henric.

CB.

0 Likes
4,963 Views
IAMDV
Luminary
Luminary

Nice post HIC.

0 Likes
4,963 Views