# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save \$200. Learn More
Contributor

## How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

We are having the requirement of top 3 and Bottom 3 avg of value.

Max of difference of Avg of top 3 and bottom 3.

Tags (5)
1 Solution

Accepted Solutions
Highlighted
Partner

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

hi

have a look at the attach example

10 Replies
Highlighted
Valued Contributor III

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

Use this for  Top:

=IF (aggr(rank( sum(Value) ,0) , Value )<= 3 , (sum(Value)/3) )

Bottomcolumn:

=IF (aggr(rank( sum(Value) ,0) , Value )> 3 , (sum(Value)/3) )

in 3 column write like(i mean subtract column 2 - column 1) 'measure 2'-'measure 1 '

Highlighted
Contributor III

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

Hi Dilip,

here is the formula for

For top 3

For bottom 3

Many Thanks

Karthik

Highlighted
Contributor

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

tried Top 3.. we are not getting the value.

Highlighted
Contributor

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

tried above expression. we are not getting the values.

Highlighted
Partner

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

hi

have a look at the attach example

Highlighted
Contributor

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

how to get in table..

Highlighted
Partner

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

what is the dimension of the table ?

Highlighted
Contributor

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

Dimension : P_id

Value :tpt (value)

Highlighted
Partner

## Re: How to get TOP 3 and Bottom 3 AVG and Max Difference value with rank function and AGGR.

can you upload a model with sample data