Rank function

Question & Answer
1

hi,

My dataset structure is:

slaesperson customer amount
Jhon A 1000
Jhon B 9000
Jhon C 12000
Daniel D 7000
Daniel E 6000
Daniel F 3000
Emma G 1500
Emma H 2000
Emma I 8000
Emma J 5000

I want to generate a rank column as a calculated field that will rank my salesperson desc by the amount metric, so the result will be-

slaesperson customer amount rank
Jhon A 1000 1
Jhon B 9000 1
Jhon C 12000 1
Daniel D 7000 3
Daniel E 6000 3
Daniel F 3000 3
Emma G 1500 2
Emma H 2000 2
Emma I 8000 2
Emma J 5000 2

I tried using rank, but when trying to give metric without any additional expressions ( rank([sum(amount) DESC]) ) within the function it changes the context of ranking in dependency of the group by, for example it if I drag the customer as well it changes the ranking in accordance to the amount of the combination of salesperson & customer.

Any idea how to solve it?

Thanks,
Amihai

2

You are using the rank function with POST_AGG_FILTER calculation level (the default) and that one is affected by the fields you choose to aggregate by in the visual. Try this:

denseRank([sumOver(amount, [salesperson], PRE_AGG) DESC], [], PRE_AGG)

Also you need denseRank since you expect rows for the same sales person to have equal ranking but the next one to have the following number.

2 Likes