topic Re: Rank issue in QlikView

Rank issue

Wed, 01 Jul 2015 13:11:41 GMT

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

Re: Rank issue

Gysbert_Wassenaar — Wed, 01 Jul 2015 13:13:18 GMT

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

Re: Rank issue

Wed, 01 Jul 2015 13:42:25 GMT

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

Re: Rank issue

sunilkumarqv — Wed, 01 Jul 2015 13:52:20 GMT

since you have only 10 empids it showing all values .

Re: Rank issue

Gysbert_Wassenaar — Wed, 01 Jul 2015 13:53:41 GMT

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.

Re: Rank issue

pratap6699 — Wed, 01 Jul 2015 16:28:59 GMT

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

Re: Rank issue

Thu, 02 Jul 2015 05:43:05 GMT

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

Re: Rank issue

sunilkumarqv — Thu, 02 Jul 2015 06:21:56 GMT

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

Re: Rank issue

pratap6699 — Thu, 02 Jul 2015 07:57:21 GMT

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

Re: Rank issue

Thu, 02 Jul 2015 08:24:35 GMT

Hi Sunil and Pratap,

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

Best Regards,

Robin

Re: Rank issue

Mon, 06 Jul 2015 08:10:20 GMT

Hi Sunil,

Hope you doing good.

As per the earlier query, we have implemented the Rank function for EmpID and their respective Salary.

There were 4 Employees which have the same salary and Ranked as 4-5 and 8-9.

Now if I Group the Ranks into A & B, so that I can differentiate their Ranks (i.e. the same salary gets divided into two groups)

Kindly assist me implementing the same, using Rank function (without using Sort function) i.e. Giving Ranks to same salary but for different Employees

EmpID, Salary, Rank, Group

Annie 8500 1, A

Proj 7000 2, A

Pith 6500 3, A

Rossel 5000 4-5, A

Zen 5000 4-5, A

Richie 4500 6, A

Paul 3000 7, A

Rahim 2000 8-9, A

Reshma 2000 8-9, B

Rose 1000 10, B

Your help would be appreciated. Thanks in advance