topic Re: Rank by group in QlikView

Rank by group

Wed, 27 Nov 2013 18:43:24 GMT

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

Re: Rank by group

Clever_Anjos — Wed, 27 Nov 2013 18:48:50 GMT

maybe aggr(Max(RATING),ID)

Re: Rank by group

Anonymous — Wed, 27 Nov 2013 18:49:44 GMT

Try expression

max(total<ID> Rating)

Edit: attached

Re: Rank by group

Wed, 27 Nov 2013 18:59:42 GMT

What about returning the top 2 Ratings?

Re: Rank by group

Clever_Anjos — Wed, 27 Nov 2013 19:05:27 GMT

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

Re: Rank by group

Wed, 27 Nov 2013 21:49:58 GMT

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

Re: Rank by group

Wed, 27 Nov 2013 22:22:02 GMT

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

Re: Rank by group

tresB — Thu, 28 Nov 2013 04:49:30 GMT

Try:

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

PFA

Re: Rank by group

VishalWaghole — Thu, 28 Nov 2013 05:25:13 GMT

Hi,

PFA

Hope it will help you.

Thanks and Regards,

Vishal Waghole

Re: Rank by group

Anonymous — Thu, 28 Nov 2013 05:37:49 GMT

perfect

Re: Rank by group

Anonymous — Fri, 29 Nov 2013 05:07:31 GMT

perfect !!