topic How to Give Rank within a Group in QlikView

How to Give Rank within a Group

pra_kale — Sat, 05 Mar 2016 06:15:41 GMT

Hi,

I want to give a rank according to Sales but not on overall but within a groups. for e.g. I want a rank within a group for sales in Traditional, Ulip and Saving.

I have tried with a below given logic but by this logic I am getting a rank overall where as my requirement is within a Group..

LOAD Product,

Sub_Product,

Sale

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD *,

AutoNumber(Sale) as Rank

Resident A Order by Sale desc;

DROP Table A;

EXIT Script

Thanks in advance.

Re: How to Give Rank within a Group

mightyqlikers — Sat, 05 Mar 2016 06:29:18 GMT

HI,

PFA.

Re: How to Give Rank within a Group

Digvijay_Singh — Sat, 05 Mar 2016 07:02:50 GMT

You can try this at front end, I don't have licensed version so cannot view qvw, below image can help, Product/SubProduct are chosen as dimension in straight table -

Re: How to Give Rank within a Group

Kushal_Chawda — Sat, 05 Mar 2016 07:33:53 GMT

your script looks ok. Only change you need is below

AutoNumber(Sale,Product) as Rank

Re: How to Give Rank within a Group

pra_kale — Sat, 05 Mar 2016 09:50:09 GMT

Thanks Kush working fine..Thanks Sam and Digvijay Too.

Re: How to Give Rank within a Group

msteedle — Tue, 30 Apr 2019 17:24:50 GMT

This approach gives the correct results, but is incredibly slow for anything but the smallest data volume. I wrote this up here: https://www.michaelsteedle.com/2019/04/ranking-within-group-quickly-in-qlik.html

TL;DR This is >99% faster:

If(Product = Previous(Product), Peek(Rank) + 1, 1) as Rank

Re: How to Give Rank within a Group

nick_creek — Wed, 17 Jun 2020 01:48:04 GMT

Thanks Mike,

Great solution. Took a little while to find this, so a 'like' might help future searchers.

Nice work & website too.

Thanks, nick