Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
count(distinct{<Status={created}>*<Customer_demand={"*"}>*-<ImportID={"*"}>} ProductID)
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
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)
try like this: count(distinct{<status={"created"}, Customer_demand={*}-{},ImportID={*}-{}>} ProductID)
Ramon, i did not understand avg and TARGET in your expression.
Uday, your expression did not work. this is what i got:
try like this: count(distinct{<status={"created"}, Customer_demand={*}-{},ImportID={}-{*}>} ProductID)
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)
count(distinct{<Status={created}>*<Customer_demand={"*"}>*-<ImportID={"*"}>} ProductID)
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.
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