How to get rank positions

[#1]

Hi everybody!

I have a question regarding ranking. I have a table with sales volume by agents.

I need to get following table when I chose particular agent. Depending on how much agent has sold he get rank in context of each city:

For example if I chose Adam I will get:

Regards Alex

[tamilarasu]

Hi Alex,

You can try below expression in straight / pivot table.

Rank(Aggr(Sum(Sales),City))

If suppose the sales value is same for two cities (For example New York 1000 and Chicago 1000), then the above expression will show the rank like 2-3 or 1-2.

If you want to assign a distinct rank for each city, you need  to use below expression.

Num(Rank(Aggr(Sum(Sales),City),4))

[#3]

Hi Tamil, thanks for response

I tried but this formula doesn't consider other agent - we get only which city is one which is second for this agent, but I need that formula calculate rating through all agent for each city and put result for selected agent.

Please ask if I was unclear.

Regards Alex

[swuehl]

You can create a straight table chart with dimension City and expression

=If(Sum(Sales)+Sum({1}0)>0,Rank(Sum(Sales)),'-')

[tamilarasu · Accepted solution]

Hi Alex,

You can check below expression.

If(GetSelectedCount(Agent)>0,Aggr(Rank(Sum({1}Sales)),City,Agent),Rank(Sum(Sales)))

If agent is not selected, then rank will be calculated based on the sales of the each city.

If this is not your expected expression, please post an another sample with expected output.

[#6]

Your solution works!

Thank you very much Tamil.

[tamilarasu]

Alex,

If you want to show hyphen for the countries which has no sales for the selected agent, you can use below expression.

If(GetSelectedCount(Agent)>0, If(Sum({1}Sales)>0,  Aggr(Rank(Sum({1}Sales)), City, Agent), '-'), Rank(Sum(Sales))) 

Have a nice day.

[#8]

yes, I got it,

Thanks again,

Alex