Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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