topic Re: Display top customers if rank is equal in QlikView

Display top customers if rank is equal

sifatnabil — Fri, 27 May 2016 10:28:26 GMT

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?

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 10:30:02 GMT

Try

=Concat(DISTINCT if(aggr(Rank(

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

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

edited rank format and function

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 10:31:14 GMT

May be this:

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

UPDATE: Added the Num() function

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 11:01:48 GMT

Stefan you stole my Concat function

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 11:07:41 GMT

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.

Re: Display top customers if rank is equal

Kushal_Chawda — Fri, 27 May 2016 11:12:29 GMT

May be

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

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 11:18:54 GMT

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 )

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 11:20:13 GMT

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

Re: Display top customers if rank is equal

Kushal_Chawda — Fri, 27 May 2016 11:29:34 GMT

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

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 11:49:45 GMT

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

Re: Display top customers if rank is equal

sifatnabil — Fri, 27 May 2016 11:57:23 GMT

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)".

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 12:01:39 GMT

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

Re: Display top customers if rank is equal

sifatnabil — Fri, 27 May 2016 12:11:05 GMT

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.

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 12:16:10 GMT

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

Just wanted to be on the safe side 😉

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 12:43:38 GMT

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), ',')

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 12:51:37 GMT

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

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 12:55:14 GMT

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

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 12:58:09 GMT

May be we need this?

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

Re: Display top customers if rank is equal

swuehl — Fri, 27 May 2016 12:59:42 GMT

Yes, it is

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

Re: Display top customers if rank is equal

sunny_talwar — Fri, 27 May 2016 13:00:16 GMT

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), ',')