topic Re: Rank function in QlikView

Rank function

Mon, 28 Oct 2013 17:04:09 GMT

Hi,

I have a following table:

ID,Subject,Marks

1,English,20

1,English,25

1,Hindi,30

1,Hindi,35

2,English,50

2,English,35

2,Hindi,60

2,Hindi,55

3,English,45

3,English,50

3,Hindi,36

3,Hindi,46

I want to calculate rank of Average Marks for ID 1/2/3 (to be given via user input) for each of the subjects. That is, my qvw file will have following straight table:

(For ID 1)

Subject - Rank

English - 3

Hindi - 3

I am attaching a qvw file - I need the correct expression in "To be Evaluated" table.

Re: Rank function

Mon, 28 Oct 2013 17:16:48 GMT

Hi,

You don't need to use rank function for this. Simple expression will list.

see the example attached.

Is this good for you ?

Best,

Re: Rank function

Mon, 28 Oct 2013 17:23:58 GMT

Hi,

I want to display the rank of the Average Marks for the ID selected in each of the subject. Currently the solution shows only Average Marks for the selected ID in each of the subject.

Re: Re: Rank function

Mon, 28 Oct 2013 18:17:12 GMT

Hi BAlaji,

I see.. you can calculate ranks in the script. See attached application.

Is this good?

Basically after loading Grades following script will calculate ranks:

....

LOAD ID,Subject,
Avg(Marks) as Avg_Mark
Resident Grades
Group By ID,Subject;

Left Join(Grades)
LOAD ID,Subject,
if(isNull(Peek('Rank_No')) or Peek('Subject')<> Subject, 1, Peek('Rank_No')+1) as Rank_No
Resident Temp_Rank
Order By Subject, Avg_Mark desc;

DROP Table Temp_Rank;

Re: Re: Rank function

Mon, 28 Oct 2013 18:25:39 GMT

Hi,

I cannot do this in script - the reason is that my original file has many more dimensions - and I want the filters applied to be reflected in the calculation.

Re: Rank function

jpapador — Mon, 28 Oct 2013 18:32:48 GMT

Is this what you are looking for?

Re: Rank function

Mon, 28 Oct 2013 18:57:36 GMT

You can still keep your other dimensions and filters they will all apply.

if you just link calculated Rank_No to your fact by Subject and ID that should be fine.

Re: Rank function

Tue, 29 Oct 2013 02:53:43 GMT

Hi jpapador,

I am not looking for simple average - as i mentioned, I am looking for rank of an ID in a subject based on average marks.

Re: Rank function

Tue, 29 Oct 2013 03:19:40 GMT

See attached QVW

Regards,

Alex

Re: Rank function

Tue, 29 Oct 2013 03:42:32 GMT

Hi Alexis,

The ranks displayed in the qvw file you attached are not correct. - For e.g., ID should have a rank of 3 in English, but I see some other value.

Re: Re: Rank function

Tue, 03 Dec 2013 08:15:44 GMT

Hi Balaji,

See attached application. It displays based on variable or based on field selection on ID.

Two options yields the same result and gives your desired result.

Basically you need an expression like below:

Avg(Aggr(rank(Avg( {$ <ID = > } Marks) ), ID, Subject))

Best,

Ersen