Qlik Community

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
dilipkumard12
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.

1 Solution

Accepted Solutions
Highlighted
Partner
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

View solution in original post

10 Replies
Highlighted
ramasaisaksoft
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
karthikoffi27se
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

If(Aggr(Rank(Avg(Value))<=3,"Your Dimension"),"Your Dimension")

For bottom 3

=If(Aggr(Rank(-Avg(Value)) <= 3,"Your Dimension"),"Your Dimension")

Many Thanks

Karthik

Highlighted
dilipkumard12
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
dilipkumard12
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
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

View solution in original post

Highlighted
dilipkumard12
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
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
dilipkumard12
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
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