Issue with Rank Function

[OmarBenSalem]

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 !

[sunny_talwar · Accepted solution]

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]

Can u please explain this Sunny?

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

Please explain

[sunny_talwar]

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]

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

Explain the logic behind it? Thanks Sunny !

[sunny_talwar]

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]

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

can u explain this part?

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

[sunny_talwar]

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.