Top and Bottom N ranks in a single table with one Input control in webi
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 .
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. 🙂