Set analysis based on null value fields

Tue, 27 Apr 2010 07:26:27 GMT

Hi all,

Currently I am looking for a method to filter selections on null value fields with set analysis.

e.g.


Test:
load
 RowNo() as ID,
 if(Product = 'C', '', if(Product = 'E', Null(), Product)) as Product,
 Sales
inline
[
Product, Sales
A, 100
A, 200
B, 300
C, 400
C, 400
D, 500
E, 600
E, 700
F, 800
G, 900
G, 1000
];

Question: "What is the sum of product with NULL value (not empty)?"

In my sample, total sum is 5900, empty sum 800, null sum 1300.

I have tried some expressions, but all failed. Finally I got it from another post "http://community.qlik.com/forums/t/16384.aspx".

"Sum({1-<Product>0>} Sales)" AND "Sum({1-<Product={*}>} Sales)" both work.

I also tried some other expressions.

But as I know, we normally use SA like this: "Sum({<Product={">0"}>} Sales)". Can we use the ">" or "<" directly after the filed "Product"? If the answer is "YES", why it does not work on the filter "ID>100"?

Can anyone explain why? Thanks a lot!

Set analysis based on null value fields

Tue, 27 Apr 2010 10:01:39 GMT

Interesting, your table shows all nulls for the lower 8 formulas.
I am using 9.00.7119.4 64 bit

Had once a similar issue and reveived the following formula for ignoring nulls:

Sum({<Product -= {}>} Sales)

which shows correct as 4600

however, using

Sum({<Product = {}>} Sales)

results in 0 which I can not understand.

For your other formulas I have not seen any example where you could limit on greater than or less than and I assume it throws an error, do not know however how we can see that.

Not much help from me but an interesting example, I hope some more comments come in

topic Set analysis based on null value fields in QlikView

Set analysis based on null value fields

Set analysis based on null value fields