Top and Bottom N ranks in a single table with one Input control in webi

[sateesh_kumar1]

Hi Team,

Purpose : How to show Top N and Bottom N records controlled by a single Input control , and show Top and Bottom records in single table ordered as per their functionality  and format them with alerts .

Desired Output

In above table top 2 records are colored with green ,ordered by descending and bottom 2 records are colored with red,ordered by ascending.

How to do :

* Create a variable that acts as Input control for ranking.

Top =2 (Default value )

* Create another variable which distinguish Top and Bottom and filter the records in table for Top and Bottom records.

Top/Bottom =If(Rank([Prod])<=[Top] ) Then "Top" Else If( Rank([Prod];Bottom)<=[Top]) Then "Bottom"

Filter the table with Top/Bottom variable inlist Top and Bottom . By default we get only Top as value for Filter , we need to add Bottom value manually to Filter.

Now we get below table . Rank column is added to table.Here values are not in order.

Next to do is to order values .(If you sort on measure /Rank we get only ascending /descending , but our purpose is to have Top values in descending and bottom values in ascending).

* Create another variable for sort

Sort =If([Top/Bottom]="Bottom";Max(Rank([Prod]))In Block-Rank([Prod]) ; Rank([Prod]))

Add Top/Bottom and Sort tables to table .

Do descending sort on Top/Bottom  and ascending sort on Sort column.

Hide Sort and Top/Bottom columns ( don't delete)

Create alert to distinguish Top and Bottom records.

..Hope you'll enjoy this trick. :smile: