Rank function

[#1]

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.

[#2]

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,

[#3]

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.

[#4]

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;

[#5]

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.

[jpapador]

Is this what you are looking for?

[#7]

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.

[#8]

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.

[#9]

Hi

See attached QVW

Regards,

Alex

[#10]

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.