Top or bottom 10 ranking by percentage - table

[richardh123]

Hi,

I am trying to display only the top / bottom 10 results based on the percentage. In this case, it is the percentage I want to base the ranking on.

The percentage growth (can be a minus figure) I am successfully calculating for each record in the table is:

(Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium))-1

The ranking I can display alongside this is:

=rank(Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium)-1)

For instance, if I have 90 records in total, I want to display 1,10 and 81-90 only. I hope that makes sense.

[Kushal_Chawda]

@richardh123  what is the dimension of table? How would you like to display ?

[richardh123]

@Kushal_Chawda  Here's a quick table of the current display. I am essentially trying to show 20 records for any given selection, the top and bottom 10 by percentage on the Growth column. I have condensed the 11th row for this purpose (11 to end), as I do NOT wish to see this on the output.

[richardh123]

@Kush  In fact, would this be easier to achieve by using two tables? One for the top 10, and one for the bottom 10.

[MayilVahanan]

Hi @richardh123 

Try like below in expression:

For Top 10

If(Rank((Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium))-1)<=10, 

Rank((Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium))-1))

or you can use the dimension limit by sorting the rank column

For bottom 10

If(Rank(-(Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium))-1)<=10, 

Rank(-(Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium))-1))

Suppose, you need to restrict based on Dimension, then try like below

=aggr(if(rank((Sum({<Year={'TY'}>}CCY_Gross_Premium)/Sum({<Year={'LY'}>}CCY_Gross_Premium))-1,4)

  <=10 ,Broker),Broker)