Rank issue

[#1]

Hi Team,

I am new to QlikView and currently stuck with Rank function.

I have a table with 2 fields, EmpID and Salary

Ex :

EmpID Sal

Rose 1000

Rahim 2000

Reshma 2000

Paul 3000

Richie 4500

Rossel 5000

Zen 5000

Pith 6500

Proj 7000

Annie 8500

I want to get the top 3 and bottom 3 salaries.

Kindly help me with the formula.

I have tried doing this, But query fails :

=(if(aggr(rank(sum(EmpID)),[SAL])>2,Sal))

=Max( aggr ( sum(EmpID)/Count([sAL]) ,SAL ))

Thanks in advance

[Gysbert_Wassenaar]

Perhaps this discussion helps: TOP 10 and Bottom 10 Customer by Revenue Amount

[#3]

Hi Gysbert,

Thanks for the inputs.

I have tried building the following query for fields - Product ID and Sales

=aggr(if(rank(sum([Product ID]))<=3 or rank(-sum([Product ID]))<=10,Sales),Sales)

It seems to list me all the values, instead top and bottom.

Kindly let me know where I am going wrong.

Thanks once again

[sunilkumarqv · Accepted solution]

since you have only 10 empids it showing all values .

[Gysbert_Wassenaar]

Perhaps you should use 3 instead of 10:

=aggr(if(rank(sum([Product ID]))<=3 or rank(-sum([Product ID]))<=10,Sales),Sales)

See attached qvw for the solution with the data you posted in your opening post.

[pratap6699]

=aggr(if(rank(sum([Product ID]))<=3 or rank(-sum([Product ID]))<=10,Sales),Sales)

[#7]

Hi All,

Thanks for all your inputs. This adds to my knowledge base.

But in result set we see matching Salary in different rows, where ideally I want to have them in same row (i.e. Ranking each salary) :

EmpID Sal

B, 2000

C, 2000

F, 5000

G, 5000

We can do it adding a Calculated Dimension or a new column ?? Can anyone help

performing the same to get individual Rank order.

Thanks again

[sunilkumarqv]

use calculated dimension and bring the same in sort descending  order expression

[pratap6699]

=(if(aggr(rank(sum(EmpID)),[SAL])>2,Sal))

[#10]

Hi Sunil and Pratap,

Thanks for your inputs. Creating a calculated dimension works for me.

Best Regards,

Robin