Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing floats in QlikSense expressions not working

I've come across this issue multiple times, but am unable to find any troubleshooting methods online.

Example:

field A is a numeric(18,2) data type

For this example, A is equal to 0.82

if(A>0,'True','False') will always return False

if(A>0.00,'True','False') will always return False

if(A<0.00,'True','False') will always return False


For whatever reason, every comparison evaluation I do on floats returns False. Has anyone discovered a workaround for this?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

And if Dimension is your chart dimension, both sides of your comparison will be equal, I assume you want the total average:

If (

(COUNT(A)/COUNT(B)) > Avg(TOTAL Aggr(COUNT(A)/COUNT(B),DIMENSION)),

rgb(250,50,50),

rgb(50,250,50)

)

The Aggregation Scope

View solution in original post

7 Replies
marcus_sommer

Qlik used IEEE 754 as number-system which allowed no big numbers only 14 digits (in some cases 15) and is has also some limitations by interpreting numbers, see: Rounding Errors.

- Marcus

petter
Partner - Champion III
Partner - Champion III

Qlik does not support any data type that is Numeric(18,2) all numbers are either integer or float. Doing inequality comparisons like you do should pose no problem. As far as I can tell it seems like your problem is elsewhere - not the comparison itself if A is a single floating value.

Where do you do the comparisons? Is it in a calculated dimension, in a chart? Context is everything and if A is a field where it could possibly return multiple values it might very well fail any type of comparison.

Could you please provide more context about where you experience your problem? Is A a field or a variable?

Not applicable
Author

Hey Peter,

I am using this comparison in the Color and Presentation tab of a bar chart in an attempt to Color by Expression.

The actual example ends up being closer to this:

COUNT(A) is a set analysis expression

COUNT(B) is a set analysis expression

If (

(COUNT(A)/COUNT(B)) > Avg(Aggr(COUNT(A)/COUNT(B),DIMENSION)),

rgb(250,50,50),

rgb(50,250,50)

)

The IF statement always evaluates as False. Even if I change the greater than sign to a less than sign.

marcus_sommer

I assume your fields A and B won't be interpreted as numeric else as strings. To check this put these fields within a listbox - a left alignment of the values indicates that they are strings and to be sure you could add an expression with something like: =isnum(A).

If they are strings you need to convert them with something like: num(num#(A, 'FormatString')) and if the lengths are greater 14 chars an additional cut like: left(A, 14).

- Marcus

swuehl
MVP
MVP

You may need to add your / a set expression also to the outer aggregation function of the advanced aggregation:

Set Analysis in the Aggr function

It could be that there are no aggr  dimension values possible in the default set.

swuehl
MVP
MVP

And if Dimension is your chart dimension, both sides of your comparison will be equal, I assume you want the total average:

If (

(COUNT(A)/COUNT(B)) > Avg(TOTAL Aggr(COUNT(A)/COUNT(B),DIMENSION)),

rgb(250,50,50),

rgb(50,250,50)

)

The Aggregation Scope

Not applicable
Author

This was it! Can't believe I missed that. Thanks Stefan and all others that answered!