topic Re: Rank & Aggregation Question in QlikView

Rank & Aggregation Question

Anonymous — Mon, 04 Sep 2017 16:52:21 GMT

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 — Mon, 04 Sep 2017 17:01:57 GMT

try this

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

antoniotiman — Mon, 04 Sep 2017 17:03:03 GMT

Hi Matthew,

maybe remove Total from Expression

Regards,

Antonio

kaushiknsolanki — Mon, 04 Sep 2017 17:18:02 GMT

Try this.

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

Regards,

Kaushik Solanki

Anonymous — Mon, 04 Sep 2017 19:37:41 GMT

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

Anonymous — Mon, 04 Sep 2017 19:46:03 GMT

Thanks, that worked