Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources. Prep for the big move: READ DETAILS
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
5,874 Views
dmohanty
Partner
Partner

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

Thanks for sharing.

0 Likes
5,874 Views
rwunderlich

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

5,874 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

5,874 Views
carbal1952
Creator II
Creator II

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

Good for you, Henric.

CB.

0 Likes
5,874 Views
IAMDV
Luminary Alumni
Luminary Alumni

Nice post HIC.

0 Likes
5,874 Views
Not applicable

your post is very hot.

0 Likes
4,245 Views
baa
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
4,245 Views
Henric_Cronström

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
4,245 Views
kalyandg
Partner
Partner

hi HIC,

really super post

Best Regards,

Kalyan

0 Likes
4,245 Views