Top 20 claims on company level

[krishna20]

Hi Friends,

I need to show top  and bottom 20 claims on company level for current year based on premium

year        Claim No   AAAAAA     BBBBB    CCCCC

2014      120120       12456          24561     51423

2014      52146           4523           47521     86241

can anyone please help me out.

Regards

krishna

[miikkaqlick]

Hi!

Easiest way is sum-premiums, sort table asc on sum and apply max number: -> best

Copy that chart and turn sort to desc. -> worst

Br,

Miikka

Climber Finland

[ajaykumar1]

Hi Kalyan,

Please post your req clearly then some one surely help you.

please post clearly even if its taking time then you will get solution easily that way you can save the time to apply in the live system.

Thanks,

Ajay

[krishna20]

Hi Miikka ,

Thanks for your reply.

Sorry , i forgot to mention that i need to show in Pivort chart.

Because , i'm adding dimensions from different tables..

Thanks & Regards,

krishna

[miikkaqlick]

Hi!

This is from my demo app. This shows Europe's top 10 products:

=If(Rank(Sum( {$<Group = {"Europe"}>} LineSalesAmount_US)) <= 10,

     Sum( {$<Group = {"Europe"}>} LineSalesAmount_US)

)

First it calculates rank. If rank is in top 10 then it will calculate sales.

Br,

Miikka

Climber Finland

[MK_QSL · Accepted solution]

Create a Pivot Table

Dimension

1) Year

2) Calculated Dimension

=IF(Aggr(Rank(SUM(Premium),4),Year,Claim No])<=3 or Aggr(Rank(-SUM(Premium),4),Year,[Claim No])<=3,[Claim No])

For both Dimensions Tick Suppress When Value is NULL

Expression

SUM({<Year = {'$(=Year(Today()))'}>}Premium)

[deepakqlikview_123]

Hi,

There is no Dimension limit tab in pivot table,

For displaying top results in pivot table you have to use rank function with aggrigation.

thanks

[krishna20]

Hi Manish,

Thank you for your reply,

Please let me know .what is '4' will do in this expression and this expression for TOP and Bottom customers right?

=IF(Aggr(Rank(SUM(Premium),4),Year,Claim No])<=3 or Aggr(Rank(-SUM(Premium),4),Year,[Claim No])<=3,[Claim No])

Thanks & Regards,

Krishna

[MK_QSL]

Consider that SUM(Premium) is same for two Claim No, Rank(Expression,4) will make sure that different Rank will be provided for each Claim NO... otherwise you will get Rank as below

1

2-3

2-3

4

5-6

5-6

7

[krishna20]

Hi Manish,

Thank Your for your reply, i got it. But i'm unable to display the ranking order.Im getting top 20.for this top top 20. i need to give rank. How can it be?

Pls help me out.

Thanks & Regards

krishna