Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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))
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))
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
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))
thank for help and explanation👍