Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Rank function in qlik sense

Hi,

If I have to display the rank on the basis of amount and alphabetically,then what would be the syntax for the same. could anyone please help me out?

name                    amount

piyush chawla        100

piyush singh           100

raj arora                  50

raj singh                 50

Then the rank should be

name                    amount     rank

piyush singh            100          2

piyush chawla          100          1

raj arora                  50            3

raj singh                 50            4

1 Solution

Accepted Solutions
MVP
MVP

Re: Rank function in qlik sense

Script:

Table:

LOAD * Inline [

name,              amount

piyush chawla,      100

piyush singh,      100

raj arora,          50

raj singh,          50

];

FinalTable:

LOAD name,

  amount,

  RowNo() as Rank

Resident Table

Order By amount desc, name;

DROP Table Table;

5 Replies
MVP
MVP

Re: Rank function in qlik sense

You can try doing this in the script if the rank doesn't have to be dynamic based on selection?

MVP
MVP

Re: Rank function in qlik sense

Script:

Table:

LOAD * Inline [

name,              amount

piyush chawla,      100

piyush singh,      100

raj arora,          50

raj singh,          50

];

FinalTable:

LOAD name,

  amount,

  RowNo() as Rank

Resident Table

Order By amount desc, name;

DROP Table Table;

neetha_p
Honored Contributor

Re: Rank function in qlik sense

Hi Aanchal,

Maybe try in UI:

=Aggr(Rank(Sum(Amount), Name,Amount)

MVP
MVP

Re: Rank function in qlik sense

You will need to calculate some sort of score which includes the amount and the alphabetic ranking of the name. you could do this by making a composite during the load:

     num(amount, '000000') & left(name  & '                    ', 20) as rankscore,

and then rank the rankscore values in the front end or in the load script.

Employee
Employee

Re: Rank function in qlik sense

Does this work ?  In the SORT i am sorting in order by:

sum(Amount) -> sort numerically -> descending

name -> sort alphabetically -> ascending

Capture.PNG