topic Aggr count distinct in QlikView

Aggr count distinct

J4N4_25 — Fri, 30 Apr 2021 22:16:22 GMT

Please have a look at the simplified example of my qvw.

I'm working with data and I want to

- count distinct number of costumers with a vaild cotract who have phone number and email, per agent
- count distinct number of costumers with a vaild cotract who have mobile number , per agent
- count distinct number of costumers with a vaild cotract who have phone number, mobile number and email, per agent
and no info needs to be excluded from counting

Re: Aggr count distinct

jwjackso — Sat, 01 May 2021 16:45:53 GMT

count distinct number of costumers with a valid contract who have phone number and email, per agent
=Sum(Aggr(Count({<contract={"*"},phone-={'no info'},email-={'no info'}>}Distinct costumer),Agent))

count distinct number of costumers with a valid contract who have mobile number , per agent
=Sum(Aggr(Count({<contract={"*"},mobile-={'no info'}>}Distinct costumer),Agent))

count distinct number of costumers with a valid contract who have phone number, mobile number and email, per agent
=Sum(Aggr(Count({<contract={"*"},phone-={'no info'},email-={'no info'},mobile-={'no info'}>}Distinct costumer),Agent))

Re: Aggr count distinct

J4N4_25 — Sat, 01 May 2021 20:28:12 GMT

Thank you so much, that worked perfectly. 🙂

what if i want to
count distinct number of costumers with a vaild cotract who have phone number OR mobile, per agent

Re: Aggr count distinct

jwjackso — Sun, 02 May 2021 00:21:36 GMT

There are different ways, my preference is the P() function. The asterisk (*) is for an intersection (AND) and the plus (+) is for a union (OR).

=Sum(Aggr(Count({<contract={"*"},costumer=P({<phone-={'no info'}>}custumer)+P({<mobile-={'no info'}>}custumer)>}Distinct costumer),Agent))

Re: Aggr count distinct

J4N4_25 — Sun, 02 May 2021 10:26:27 GMT

thank for help and explanation👍