topic Re: Comparing floats in QlikSense expressions not working in App Development

Comparing floats in QlikSense expressions not working

Fri, 17 Mar 2017 15:05:57 GMT

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?

Re: Comparing floats in QlikSense expressions not working

marcus_sommer — Sun, 19 Mar 2017 18:16:13 GMT

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

Re: Comparing floats in QlikSense expressions not working

petter — Sun, 19 Mar 2017 22:37:28 GMT

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?

Re: Comparing floats in QlikSense expressions not working

Tue, 21 Mar 2017 13:57:38 GMT

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.

Re: Comparing floats in QlikSense expressions not working

marcus_sommer — Tue, 21 Mar 2017 14:48:04 GMT

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

Re: Comparing floats in QlikSense expressions not working

swuehl — Tue, 21 Mar 2017 14:52:40 GMT

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.

Re: Comparing floats in QlikSense expressions not working

swuehl — Tue, 21 Mar 2017 14:57:43 GMT

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

Re: Comparing floats in QlikSense expressions not working

Tue, 21 Mar 2017 17:02:53 GMT

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