ranking within a rank

[Anonymous]

in a straight table, how do I rank within a rank?

price_tier	item	rank
1	a	1
1	b	2
1	c	3
1	d	4
1	e	5
1	f	6
2	g	1
2	h	2
2	i	3
2	j	4
2	k	5
2	l	6

[antoniotiman]

Hi William,

try this

LOAD price_tier,item,Autonumber(RowNo(),price_tier) as rank

From Table;

Regards,

Antonio

[sunny_talwar]

Another option

Table:

LOAD price_tier,

    item

FROM

[https://community.qlik.com/thread/272434]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(price_tier = Previous(price_tier), RangeSum(Peek('rank'), 1), 1) as rank

Resident Table

Order By price_tier;

DROP Table Table;

[effinty2112]

Hi William,

If you load without your rank column in an arbitrary order

Data:

LOAD * INLINE [

    price_tier, item

    2, g

    2, j

    1, c

    1, a

    2, k

    1, f

    2, i

    1, d

    1, e

    2, l

    2, h

    1, b

];

This straight table will give the rank of item, based on alphabetical order, within the price tier.

item	Aggr(RowNo(),price_tier,(item,TEXT, ASCENDING))
a	1
b	2
c	3
d	4
e	5
f	6
g	1
h	2
i	3
j	4
k	5
l	6

Cheers

Andrew

[Anonymous]

Thank you all for the quick replies, but i need to do this in a chart.  There are dynamic criteria that will change the rankings.  everything works fine when I have certain selections made, buy when I begin to clear them the rankings change, and I would like them to stay the same.  I have tried aggr() around the rank, but thus far it has had the same result.  I appreciate your help!

[sunny_talwar]

Would you be able to share a sample of what you have thus far?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[Anonymous]

Unfortunately, I cannot share the data, but here is part of the equation.

aggr(nodistinct rank(total RangeAvg(...)),market,category,spirits_price_tier,brand_family,brand,size)

This works when I have market,category and spirits_price_tier selected, but when i clear one out the ranks change.

[sunny_talwar]

Well I kind of see the reason for changing Rank, but I am not sure what you want to get and that's why a sample would have helped here... Can you mock up some data which match your real scenario and then share the expected output from it?

[Anonymous]

Attached is some dummy data. The two tabs represent the desired result and what is happening when more than one market, category or price tier is possible.

[sunny_talwar · Accepted solution]

Try this

Aggr(Rank(RangeAvg([index 1], [index 2], [index 3], [index 4], [index 5], [index 6], [index 7], [index 8])), Key, RecId)

Where Key and RecId are created in script like this

Table:

LOAD RowNo() as RecId,

AutoNumber(market&'|'&category&'|'&[spirits_price_tier 1]) as Key,

market,

    category,

    [spirits_price_tier 1],

    size,

    brand,

    [index 1],

    [index 2],

    [index 3],

    [index 4],

    [index 5],

    [index 6],

    [index 7],

    [index 8]

FROM

[..\..\Downloads\sample_data.xlsx]

(ooxml, embedded labels, table is [yellow is desired result]);