Random Thoughts

Views on life

WEBI – How to display “Others” in country field based on Rank

Posted by Hemanta Banerjee on November 8, 2010


One of the frequent questions I come across is – I want to view the top 5, however I want everything else that is not in the top 5 to be placed in 1 bucket called others. I ran into the same question on the BOB board today and since it was simple enough here comes the solution.

Getting the rank within WEBI is quite simple using the RANK function. The syntax for the function is RANK(measure name; dimension name; top|bottom). For example of I wanted to find out the rank by sales I could define RANK([Sales Revenue], [City], top).

In order to make my life easier I have defined variable called Rank as shown below.

image

I can use that in a report filter to get only the top 3 regions by Sales Revenue.

image

Now to display all other regions I will use the WEBI function NOFILTER. This will return the total sales across all the regions irrespective of the filter. Using the formula below I can get the sales for all the regions that are not in top 3.

image

By placing this in the footer of the table I can get the report to show the sales for others.

image

Advertisements

2 Responses to “WEBI – How to display “Others” in country field based on Rank”

  1. affiliate marketing…

    […]WEBI – How to display “Others” in country field based on Rank « HOW TO Business Intelligence[…]…

  2. seo said

    seo…

    […]WEBI – How to display “Others” in country field based on Rank « HOW TO Business Intelligence[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: