7 Replies Latest reply: Feb 26, 2018 8:17 AM by Sunny Talwar

# 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 !

• ###### 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))))

• ###### Re: Issue with Rank Function

Can u please explain this Sunny?

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

• ###### 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...

• ###### 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?

• ###### 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.