Count Distinct clients by rank

[Anonymous]

Hello,

I would like to get a count distinct customer based on the rank of the family, only where the rank= 1. The objective is to obtain a histogram counting the distinct clients according to the preferred family

thanks you

[sunny_talwar]

Is this what you are looking to get?

Calculated dimension

=Aggr(If(Rank(Sum(turnover)) = 1, family), customer_code, family)

[Anonymous]

Thank you is that I have just a small difference that must be linked to the rank equal to 1-2?

[sunny_talwar]

May be try this

=Aggr(If(Rank(Sum(turnover)) < 2, family), customer_code, family)

[Anonymous]

Not really the problem remains but it is the result of the perfect equality for a same client on two different families but the cases are marginal and the result is acceptable. thanks a lot for your help

[sunny_talwar]

May be we can fine tune the expression if you can tell us the exact output you wish to see based on the supplied data

[Anonymous]

The problem is the difference between the total of the clients on the preferred families (5022) and the total of the customers equal to 5031. Even if we take <2 the problem is more serious because the sum is greater than the total number of customers (5044). I think the problem lies on the perfect equalities of a client on two families. The objective is to have an equal distribution to the total of clients namely 5031. thank you for your help

[sunny_talwar]

The problem is that we are doing a distinct count of customers and some of those customers may fall in more than one family.....  but if you look here, the total is in fact 5031 with < 2

Now the question is that if a customer falls under to family, where should it be added in the row wise count?

[Anonymous]

I think I should add another criterion like the margin to avoid cases where rank is equal 1-2 but it becomes complicated? In any case your help has been very useful to me thanks again

[sunny_talwar · Accepted solution]

I am glad we were able to help. Please consider closing this thread if you are satisfied, or let us know how else we can help.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny