Regarding top 3

[sarfaraz_sheikh]

Dear ALL,

I want to select top 3 rankers from given table as below:-

Location                    Total Inward

Banglore                    1

Gaziyabaad                5

Thane                         3

Chennai                      20

Delhi                          25

Bhopal                       16

Expected output :-

Location                       Total Inward

Delhi                              25

Chennai                          20

Bhopal                           16      

I want the output should return Top 3 order from normal one...in case of oracle i used rownum >= number( specified by us)....how i can achieve the same in QlikView.

Sarfaraz

[its_anandrjs · Accepted solution]

Hi,

Take calculated

Dim:- =aggr(if(Rank(sum(Total_R))<=3,Location),Location)

Expre:- =sum(Total_R)

Note:- And in dimension select suppress when value is null option selected.

Regards

Anand

[sarfaraz_sheikh]

Dear All,

Thanks for your response...but still i am confused ...my QVW file is attached here for your reference.

Dear amit you answer is correct...but what is this rank() and Aggr() doing here ...can you please explain me in brief..

Sarfaraz

[amit_saini]

Hi,

Rank Function:

The rank function will identify the place a row has in a table based on the field you specify.

I have a current example of a call center where we rank each member in the team according to the value of the sales they make in a month.

By making the rank the primary sort field we get:

Rank Name Sales

1 Richard Jones £200,000

2 Richard Smith £180,000

3 Richard Name £150,000

This is a simple example where the rank expression is simply RANK(Sales).

For Aggr() function please see this link:

QlikView Technical Brief - AGGR.docx

Thanks,
AS

[its_anandrjs]

Hi Sarfaraz,

If you use Rank you get the rank value to your values for this see the example.

If You use =Rank(Total_R) it will show 1,2,3...ranking to your rows and

if you use =if(Rank(sum(Total_R))<=3,Location) this will show top 3 Location but as you use it in the dimension you need to aggregate it by Location for display Locations

=aggr(if(Rank(sum(Total_R))<=3,Location),Location)

Location	=sum(Total_R)	=Rank(Total_R)
Delhi	25	1
Chennai	20	2
Bhopal	16	3
Gaziyabaad	5	4
Thane	3	5
Banglore	1	6

Regards

Anand

[sarfaraz_sheikh]

Hi anand,

Thanks a lot ........It is working fine as expected ....but one request to you ...can please explain me what aggr() function as i do not have the clarity about this function ...

Sarfaraz

[tyagishaila]

Hi Sarfaraz,

Try  it,

if(aggr(rank(count(isnull(INWD_CREATOR))), LOCATION)<=3, LOCATION)

[amit_saini]

Hi Sarfaraz ,

It is not possible to define this in two line that's why i have shared that link with you, if you really want to know what can a aggr function can do please follow the same.

Thanks,
AS

[its_anandrjs]

Hi,

You have to do the practice for this and with any samples you learn by testing the data. Basically when you use any Aggregation function in the dimension you have to aggregate the expression to get desire rows.

Suppose in the dimension you write expression you get nothing

=if(Rank(sum(Total_R))<=3,Total_R)

But same if you write in the expression

=if(Rank(sum(Total_R))<=3,Total_R)

And dimension is Location you get three rows because when you use it expression it get aggregated to the Location dimension

Same if you use it like

Dim:- =Aggr( if(Rank(sum(Total_R))<=3,Total_R),Location)

Here you need to aggregate it with Location in the dimension and expression is used

Expre:-=sum(Total_R)

Regards

Anand

[ecolomer]

See this