Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

OmarBenSalem
Esteemed Contributor

Issue with Rank Function

Hi all ( stalwar1‌ )

I have sthing in mind but I can't figure out a way to do it; Let me explain:

I have one dimension : ClientName

I have on measure: sum({1}Hits)


I've created a text field: its goal is to give back the rank of each clientName once selected :

subfield(aggr(rank( sum({1} NbHits), ClientName),ClientName),'-')


This will work, when we select one ClientName, It returns its Rank.

But now, I got yet another requirement which is :

I select 3 clients for example: I sum up their Hits. and see their combined rank (if these 3 clients were 1 client, where would they Rank?)

Hope that was clear?

Ps: I'm not even sure this is possible!

Hope someone would help !

1 Solution

Accepted Solutions

Re: Issue with Rank Function

Can't find a straight forward way... but a work around could be like this

=Only({1}Aggr(If(Sum({1} Sales) < Sum(TOTAL Sales) and Alt(Above(Sum({1} Sales)), 1E10) > Sum(TOTAL Sales), Rank(Sum({1} Sales))), (Client, (=Sum({1} Sales), DESC))))

7 Replies

Re: Issue with Rank Function

Can't find a straight forward way... but a work around could be like this

=Only({1}Aggr(If(Sum({1} Sales) < Sum(TOTAL Sales) and Alt(Above(Sum({1} Sales)), 1E10) > Sum(TOTAL Sales), Rank(Sum({1} Sales))), (Client, (=Sum({1} Sales), DESC))))

OmarBenSalem
Esteemed Contributor

Re: Issue with Rank Function

Can u please explain this Sunny?

ps : sometimes, it does return a value; sometimes not :

Capture.PNG

Capture.PNG

Please explain

Re: Issue with Rank Function

This might just be related to the function of my if condition check... I am just checking for > and <... you might need to check >= < or > <= or >= <=... it all depends on your requirement and how the data is...

OmarBenSalem
Esteemed Contributor

Re: Issue with Rank Function

I see; now, can plz break up ur expression?

Explain the logic behind it? Thanks Sunny !

Re: Issue with Rank Function

I am comparing the total sales from the sales from all other clients. Pick the Rank of the client where the condition is met.

Combined Sale is greater than the sales from the current row client and Combined Sale is less than the sales from the previous row client

Does that make sense?

OmarBenSalem
Esteemed Contributor

Re: Issue with Rank Function

I see !!! That's a brilliant way to do things !

can u explain this part?

and Alt(Above(Sum({1} Sales)), 1E10)

Re: Issue with Rank Function

This is just to handle if the combined rank of selected client is ranked 1. Because Above() will lead to null, I am replacing it with a really big number which will give me a true.

Community Browser