Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)".
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 |
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.
Yeah, I believe the <2 can be replaced by =1.
Just wanted to be on the safe side 😉
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), ',')
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 |
Then the other expression that you proposed seems to show 1.5. Is it showing differently for you then what I have pasted below?
May be we need this?
=Num(Rank(Sum(Sales), 1, 1))
Yes, it is
But still, the <2 should catch this kind of issue
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), ',')