Rank by group

[#1]

Hi,

I have an excel sheet with two columns e.g.

ID	RATING
1	-20
1	50
1	-1
1	0
2	100
2	-15
2	98

I want to return the top rank by ID so ID 1 would return 50, ID 2 100 etc.

any ideas how to code this as a calculated dimension?

Thanks

[Clever_Anjos]

maybe aggr(Max(RATING),ID)

[Anonymous]

Try expression

max(total<ID> Rating)

Edit: attached

[#4]

What about returning the top 2 Ratings?

[Clever_Anjos]

max(total<ID> Rating, 1) and max(total<ID> Rating, 2)

[#6]

I actually need the rank number to be returned.

e.g.

I want to return the rank number based on max rating grouped by ID so ID 1 would return the following:

50  rank 1

-20 rank 2

etc.

I think I need to use the aggr and rank function in a calculated dimension but I can't seem to get the syntax correct.

Thanks guys

[#7 · Accepted solution]

Hi Coran

If you have the ID and rating as column 1 and column 2, then the expression

=rank(rating)

will automatically be grouped by ID - but it must be sorted by ID (or the group name)

Does this work for you?

Regards,

Erica

[tresesco]

Try:

=Aggr(If( Rank(Rating)<3, Rating),ID, Rating)                      // as calculated dimension

PFA

[VishalWaghole]

Hi,

PFA

Hope it will help you.

Thanks and Regards,

Vishal Waghole

[Anonymous]

perfect