Create Ranks in Script Editor - QlikSense

[vidyasagar159]

Hi All,

Hope you guys are doing great.

I would like to create a Ranks for the below sample data in QlikSense using Script Editor. I tried RowNo() but unsuccessful. Can some please help me with the logic?

Thanks in Advance!!!

Product Category	Product Name	Amount	Ranks for Products In Each Category	Ranks for Categories
A	A1	$100	1	1
A	A2	$200	2	1
A	A3	$300	3	1
A	A4	$400	4	1
A	A5	$500	5	1
B	B1	$100	1	2
B	B2	$200	2	2
B	B3	$300	3	2
B	B4	$400	4	2
B	B5	$500	5	2
B	B6	$600	6	2

[sunny_talwar]

How are you creating the Ranks for Categories here? Is this based on Sum(Amount)? The lower the Sum(Amount), the lower the rank?

[sunny_talwar · Accepted solution]

You can give this a shot

Table:
LOAD * INLINE [
    Product Category, Product Name, Amount
    A, A1, $100
    A, A2, $200
    A, A3, $300
    A, A4, $400
    A, A5, $500
    B, B1, $100
    B, B2, $200
    B, B3, $300
    B, B4, $400
    B, B5, $500
    B, B6, $600
];

FinalTable:
LOAD *,
	 If([Product Category] = Previous([Product Category]), RangeSum(Peek('Ranks for Products In Each Category'), 1), 1) as [Ranks for Products In Each Category],
	 If([Product Category] = Previous([Product Category]), Peek('Ranks for Categories'), RangeSum(Peek('Ranks for Categories'), 1)) as [Ranks for Categories]
Resident Table
Order By [Product Category], [Product Name];

DROP Table Table;