How to include and exclude null values using SET Analysis

[Anonymous]

Requirements: count distinct ProductID for below condition

1) include only Created status

2) include only when Customer_demand is not null

3) exclude when ImportID is not null

[Anonymous]

I am using the IF expression as below and it seems to be working.

Count (DISTINCT if( Status='created' and  not isnull(Customer_demand) and isnull (ImportID), ProductID))

But I would like set analysis to do this. with above statement,  i am not comfortable with isnull (ImportID) part very much, because it will include all records where ImportID is a null value. Please advise

[ramoncova06]

this post has a couple of examples

https://community.qlikview.com/docs/DOC-3155/version/7

I normally use nullcount

=avg({<AQ={"=nullcount(TARGET)=0"}>}TARGET)

[#4]

try like this: count(distinct{<status={"created"}, Customer_demand={*}-{},ImportID={*}-{}>} ProductID)

[Anonymous]

Ramon, i did not understand avg and TARGET in your expression.

Uday, your expression did not work. this is what i got:

[#6]

try like this: count(distinct{<status={"created"}, Customer_demand={*}-{},ImportID={}-{*}>} ProductID)

[ramoncova06]

that was just an example of what I have used before, try with this expresion

count(distinct{<

status={"created"}, ProductID = {"=nullcount(Customer_demand)=0"} >

*

<status={"created"}, ProductID = {"=nullcount(ImportID)=0"} > }

ProductID)

[maxgro · Accepted solution]

count(distinct{<Status={created}>*<Customer_demand={"*"}>*-<ImportID={"*"}>} ProductID)

[Anonymous]

Ramon, i tried your expn; result was 0. so I added minus infront of {"=nullcount(ImportID)=0"} it gave me

it is missing XYZ002. otherwise it would have been the correct ansswer. because the task is to have those records with Created status, include if Cust_demand is NOT null, include if ImportID IS null.

[Anonymous]

Massimo, you nailed it. That was the answer. I have broken down the expn to understand your key.

count(distinct{<Status={created}> : this part I get it. Clear

*<Customer_demand={"*"}>* : here, by {'*'}, you mean include all non-null values for Cust_demand..?? what do ** mean at either ends..?

-<ImportID={"*"}>} ProductID) : here I understand that u r doing opposite of Cust_demand, becoz we want to count null values. but there is no ** on either sides. please explain