Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Create Ranks in Script Editor - QlikSense

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 CategoryProduct NameAmountRanks for Products In Each CategoryRanks for Categories
AA1$10011
AA2$20021
AA3$30031
AA4$40041
AA5$50051
BB1$10012
BB2$20022
BB3$30032
BB4$40042
BB5$50052
BB6$60062
Labels (3)
1 Solution

Accepted Solutions
Highlighted

Re: Create Ranks in Script Editor - QlikSense

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;

View solution in original post

2 Replies
Highlighted

Re: Create Ranks in Script Editor - QlikSense

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

Highlighted

Re: Create Ranks in Script Editor - QlikSense

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;

View solution in original post