How to Give Rank within a Group

[pra_kale]

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..

A:

LOAD Product,

     Sub_Product,

     Sale

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

LOAD *,

AutoNumber(Sale) as Rank

Resident A Order by Sale desc;

DROP Table A;

EXIT Script

Thanks in advance.

[mightyqlikers]

HI,

PFA.

$@

[Digvijay_Singh]

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 -

[Kushal_Chawda · Accepted solution]

your script looks ok. Only change you need is below

AutoNumber(Sale,Product) as Rank

[pra_kale]

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

[msteedle]

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

[nick_creek]

Thanks Mike,

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

Nice work & website too.

Thanks, nick