Aggr count distinct

[J4N4_25]

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

[jwjackso · Accepted solution]

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))

[J4N4_25]

 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 

[jwjackso · Accepted solution]

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))

[J4N4_25]

thank for help and explanation👍