Display top customers if rank is equal

[sifatnabil]

Hi all,

I have this expression to return the top customer for the latest session who had the highest count of orders (Id) -

=only(if(aggr(Rank(

count(distinct {<SessionId={'$(LatestSession)'}>}Id)),

CustName)=1,CustName))

However today, we had 2 customers with the same top count of orders (i.e. rank 1) - in this scenario this function fails. How can I amend this function to return both top ranked customers?

[sifatnabil]

Hi swuehl‌ & stalwar1‌, I used Sunny's because there could be 2 or 3 or 4 etc. customers who have the same highest order count, and would like to list them all. What is the difference between these 2 expressions otherwise? AFAIK, when doing a rank of count(distinct Id), there can only be 1 top rank (i.e. the highest count), and I would like to just retrieve all customers linked to that top rank. If highest count = 100, and 3 customers had 100, then I want to list all 3 customers as the "Top customer(s)".

[swuehl]

Understood.

Doesn't mine rank expression deliver this requested result?

As I mentioned, there might be issues with Sunnies expression in special cases:

ID	=Num(Rank(Sum(sale)))	Rank(Sum(sale),0,1)	Sum(sale)
1001	2	1	40
1003	2	1	40
1004	2	1	40

[sifatnabil]

Interesting. Yours also works - just realized it will also work if there are more than 2 customers; I confused the "<2" to mean show only top 2. In that case yours would be correct, thanks.

[swuehl]

Yeah,  I believe the <2 can be replaced by =1.

Just wanted to be on the safe side 😉

[sunny_talwar]

As I mentioned, there might be issues with Sunnies expression in special cases:

Stefanies I have a question here.

I used this data

Table:

LOAD * Inline [

Client, Sales

A, 10

B, 10

C, 3

];

and I am seeing this

I am not seeing 1.5. Would you be able to point out what am I missing here?

This did not work Sifat‌?

=Concat(If(Aggr(Num(Rank(Count(Distinct {<SessionId={'$(LatestSession)'}>}Id))),CustName)=1,CustName), ',')

[swuehl]

As I tried to explain, just a num(Rank(Sum(Sales))) will not work (i.e. returning 1 for A and B) in special cases, e.g. only A and B are selected:

Client	Sum(Sales)	=Num(Rank(Sum(Sales)))	Rank(Sum(Sales),0,1)
	20
A	10	1,5	1
B	10	1,5	1

[sunny_talwar]

Then the other expression that you proposed seems to show 1.5. Is it showing differently for you then what I have pasted below?

[sunny_talwar]

May be we need this?

=Num(Rank(Sum(Sales), 1, 1))

[swuehl]

Yes, it is

But still, the <2 should catch this kind of issue

[sunny_talwar]

Without selection

With Selection:

So may be this will work the best?

=Concat(If(Aggr(Num(Rank(Count(Distinct {<SessionId={'$(LatestSession)'}>}Id)), 1, 1),CustName)=1,CustName), ',')