Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
lironbaram
Partner - Master III
Partner - Master III

hi

have a look at the attach example

View solution in original post

11 Replies
ramasaisaksoft

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 '

karthikoffi27se
Creator III
Creator III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

tried above expression. we are not getting the values.

lironbaram
Partner - Master III
Partner - Master III

hi

have a look at the attach example

Anonymous
Not applicable
Author

how to get in table..

lironbaram
Partner - Master III
Partner - Master III

what is the dimension of the table ?

Anonymous
Not applicable
Author

Dimension : P_id

Value :tpt (value)

lironbaram
Partner - Master III
Partner - Master III

can you upload a model with sample data