topic Re: distinct count-exclude nulls or blanks or zero in QlikView

distinct count-exclude nulls or blanks or zero

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have the below dataset and I want to show distinct count of Products(but exclude products where the exp field has null, blank or 0 values)

Data-->

Category Product exp sales

cat1 A 34 100

cat1 A 20

cat1 B 100

cat1 C 10 50

cat1 D 0 50

In straight table i am using Category as dimension and below expression

Count({<exp =- {"=IsNull(exp)","=len(exp)",0}>} DISTINCT Product) //not working

Please help

Anil_Babu_Samineni — Wed, 08 Nov 2017 11:51:20 GMT

This?

Count({<exp -= {"=IsNull(exp)","=len(exp)=0"}>} DISTINCT Product) //not working

surajap123 — Wed, 08 Nov 2017 12:04:21 GMT

The expression is returning 3. As you can see only 2 distinct products satisfies the condition.

prat1507 — Wed, 08 Nov 2017 12:06:07 GMT

Maybe this

Count({<exp -= {"=IsNull(exp)","=len(exp)=0","0"}>} DISTINCT Product) //not working

sunny_talwar — Wed, 08 Nov 2017 12:09:35 GMT

How about this

Count({<Product = {"=Sum(exp) = 0"}>} DISTINCT Product)

Anil_Babu_Samineni — Wed, 08 Nov 2017 12:10:23 GMT

What are you getting when you use these?

Count({<exp -= {'-','0'}>} sales)

Count({<exp = {"=Sum(exp) > 0"}>} sales)

surajap123 — Wed, 08 Nov 2017 12:10:28 GMT

Thanks sunny