Rank & Aggregation Question

[Anonymous]

I'm trying to calculate a rank from the Rating column that is aggregated over Date, Time and Location fields.

This is my current table where Rank = rank(TOTAL(Rating),0,1)

Date	Time	Location	Name	Rating	Rank
2017-06-25	13:00	London	Matt	123	4
2017-06-25	13:00	London	Tim	122	5
2017-06-25	13:00	London	Bob	121	6
2017-06-24	15:00	London	Frank	156	1
2017-06-24	15:00	London	Jim	142	2
2017-06-24	15:00	London	Pat	134	3

What I need to achieve is:

Date	Time	Location	Name	Rating	Rank
2017-06-25	13:00	London	Matt	123	1
2017-06-25	13:00	London	Tim	122	2
2017-06-25	13:00	London	Bob	121	3
2017-06-24	15:00	London	Frank	156	1
2017-06-24	15:00	London	Jim	142	2
2017-06-24	15:00	London	Pat	134	3

I've tried using rank and aggr but just cannot get it to work.

Any help appreciated.

Thanks

[Kushal_Chawda]

try this

rank(TOTAL <Date> only(Rating),0,1)

[antoniotiman]

Hi Matthew,

maybe remove Total from Expression

Regards,

Antonio

[kaushiknsolanki · Accepted solution]

Try this.

Aggr(Rank(Sum(Rating)),Date,Time,Name)

Regards,

Kaushik Solanki

[Anonymous]

Thanks, but that returns Rank as 1 for all rows,

[Anonymous]

Thanks, that worked