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

[Anonymous]

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.

[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]

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]

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

[Anonymous]

tried above expression. we are not getting the values.

[lironbaram · Accepted solution]

hi

have a look at the attach example

[Anonymous]

how to get in table..

[lironbaram]

what is the dimension of the table ?

[Anonymous]

Dimension : P_id

Value :tpt (value)

[lironbaram]

can you upload a model with sample data