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?

[swuehl · Accepted solution]

Try

=Concat(DISTINCT if(aggr(Rank(

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

CustName)<2, CustName), ', ')

edited rank format and function

[sunny_talwar]

May be this:

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

UPDATE: Added the Num() function

[sunny_talwar]

Stefan you stole my Concat function

[swuehl]

Did I ?

I haven't actually seen your post before posting and editing mine.

Initially I just haven't read the request carefully enough to see that the OP wanted to see both leading customers.

[Kushal_Chawda]

May be

=Only({<CustName={"=Rank(count(distinct {<SessionId={'$(LatestSession)'}>}Id),4)=1"}>}CustName)

[sunny_talwar]

I am sorry for making you explain. My intentions were not to do that. I just feel so good when my responses come so close to yours that I tend to do things like these (claiming a function to be my own )

[sunny_talwar]

I think you mean Concat() instead of Only()

[Kushal_Chawda]

I think only will also work, not sure, can you check?

[swuehl]

I am sorry for making you explain. My intentions were not to do that. I just feel so good when my responses come so close to yours that I tend to do things like these (claiming a function to be my own )

Hi Sunny, don't need to be sorry.

But please note that there are slight differences between the two expressions (specifically w.r.t the rank()), for example when the two leading customers are the only two customers in the selection:

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

Hence, I think you should test for <2 if you want to make it more stable (or use different rank options).

Regards,

Stefan