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:

[Former Member]

hi Sateesh, Thanks for sharing good information

[Former Member]

Good document for begginers.

[former_member197773]

Nice Tip Sathish. Thanks for sharing good stuff. It is helpful for beginners...

[former_member201536]

Thank you very much for the article but can you please help me in "Hiding" a column in webi.. googled but unable to find a way to hide a column..

I am using Business Objects 3.1

[former_member201536]

For a beginner like me it was a very helpful article..

Thanks.

[sateesh_kumar1]

Hi Siva ,

There is no direct way to do. We need to create an alert , which has text and background color as white.

http://www.dwbiconcepts.com/reporting-a-analysis/25-business-objects/134-conditional-column-hiding-b...

[former_member201536]

Thanks Sathish for your quick response...

This article is really helpful

[former_member201536]

I have one doubt here.... if we hide somewhere in between there will be white space that is displayed... is there any way that "Hidden part" can be moved to the end of the table..

[sateesh_kumar1]

we need to insert the columns to be hidden at the end.

[Former Member]

awesome!

[Former Member]

Thank you very much for the article, it is very helpful.

[former_member201488]

Why are the bottom two values sorted in ascending order? What's the rationale behind that?

[sateesh_kumar1]

Hi Neil,

When we apply bottom ranking we'll get values from least to highest.

[former_member201488]

Yes, I get it, but I don't understand why? Don't you think it's a bit counterintuitive? You're showing the Top and Bottom x amount, so naturally you would expect them to be in order - so top & bottom 2 from a list of 10 you would expect 1,2,9,10. Your mechanism shows them as 1,2,10,9 - and I don't understand a business reason for wanting to display in that order? That's what I'm curious about - maybe it's a western thing, but I would expect a ranking to be in order - that's all.