Feature or bug?

[johanfo2]

I'm struggeling to understand what I'm doing wrong here.

I have a table with Brokers and prices (and more columns, but that is not of relevance).  When I set up a chart table to count how many rows that have Price = 0 per broker, I have tried the following expressions:

=sum(if(Price=0, 1,0)) <--- Does work

=count({1<Price={"<0.00001"}>} Broker)   <-- Does work

=count({1<Price={0}>} Broker)  <----  Doesn't work

What I find amazing is that when I use set analysis, I can't compare with 0, even though the data in the table is 0.  I have to compare it with extremly small number.  When I use the fist expression listed above, I can successfully compare it with zero. 

Why?

[Miguel_Angel_Baeyens]

Hi Johan,

What version of QlikView are you running? It does not seem as an expected behavior, but it would be very useful if you could upload a sample set of data so we can check further. Are they null values or empty values or actual 0 values?

Hope that helps.

Miguel

[johanfo2]

I run QV 10 SR5.   I will try to upload a sample!

[johanfo2]

Here is the upload example.

[flipside]

=count({1<Price={0.00}>} Broker)

The above seems to work, but only with 2 decimal places.

flipside

[johanfo2]

Amazing!  I thought I had tried all possible variations, except that!   This must be a serious bug?  Howcome it remains in SR5?

Update:  I tried this trick (0.00) in my main document, however it didn't work there!!  The whole thing seems unreliable to the bone.

[whiteline]

Cool...

You can change number format for 'Price' instead of set to get everithing work.

[rwunderlich · Accepted solution]

Let's call it a Feature. The  value inside {} is a search expression, and follows the rules for search expressions. A value by itself in a search expression indicates a string search, therefore you will only match on the exact string "0". Not "0.0" or "0.00" etc.

So figure out the correct syntax for numeric search by practicing in a listbox. The search in a listbox would be:

=Price=0

So your correct set expression is:

=count({1<Price={"=Price=0"}>}Broker)

Easy, right?

-Rob

http://robwunderlich.com

[johanfo2]

Thanks a lot for your answer. 

I have to say that while I really like Qlikview, and haven't been close to finding any other software which has its responsivness and features, certain elements of qlikview are just plain insane!

[flipside]

Investigating a bit more, because I agree this is a dodgy feature to me, it looks like Qlikview treats a field's zero values based on the format of the first zero value it finds in that field. So ...

A, 0.001

B, 1.001

C, 0.00

C, 0.000

C, 0

... means you can match Price = {0.00} and it will count all values of C the same.  If you had C, 0.000 as the first zero value, you have to match Price = {0.000}.

If you go to Document Properties > Number, you can override this by selecting the format specified, say set everything to 3 decimal places, then match Price = {0.000} regardless of the order of zeroes.