Jan
15
2016

Top N and Bottom N View on the Same Worksheet

2This article helps you find the top and bottom N within the whole data set, and not in a subset of the data. Here, we will create two bar graphs in same work sheet, one is showing Top 5 values and the other is showing Bottom 5 values.

Problem Statement:

Suppose you have a large sets of customer data that you want to visualize Like Top N and Bottom N customers based on their sales .You might find that limiting the amount of information displayed to an important subset of records helps you work with and answer questions about the data more effectively.

Steps to Reproduce:

Let’s use the sample file, Sample – Superstore Sales (Excel). Open a new worksheet and select Sample – Superstore Sales (Excel) as the data source.

Once the data is loaded, perform the following steps for getting the desired result:

METHOD 1

Step 1:

Using the Superstore Sales connection, create a basic bar chart showing the Sum of Sales per Customer.

Step 2:

Add [sales] to the column shelf and [Customer] to the row shelf.

Step 3:

Before creating a calculated field, let’s make a Parameter that will allow users to quickly toggle between views with different variables.

1

Step 4:

Create two calculated Fields as demonstrated below.

Top N filter: Rank_Unique (Sum (Measure)) <= [User Defined Parameter]

2

Bottom N filter: Rank_Unique (-Sum (Measure)) <= [User Defined Parameter]

3

Rank_Unique: Duplicate values are given unique rankings, according to the direction in which the ranking is being computed.

4

For example: Lets create an excel sheet by using a same data and then use this as a tableau data Source.

5
A review of rank functions in Tableau is here.

Step 5:

Drag Top N filter and Bottom N filter in the row shelf. These two Boolean formula compares the Customer ranking that helps in deriving the True-False condition for the top 5 Customers by Sales.

6

We can combine these two formula into a single Formula

7

METHOD 2

Step 1:

We can also accomplish this Top N or Bottom N result by couple of different ways:

One is to use a table calculation filter using INDEX () and SIZE () another is with sets. Drag these two calculated field in rows shelf we can achieve this. In this case SIZE () returns the total number of members of the domain.

8

Step 2:

Create a calculated Field [Top N – Bottom N] by using the below formula.

[Top N – Bottom N]:

If INDEX () <= [Select N Values] THEN "Top N Values"

ELSEIF INDEX ()>= (SIZE () - [Select N Values]) THEN “Bottom N Values"

ELSE   "Others"

END

18

Step 3:

Drag the [Top N – Bottom N] calculated Field in the Filter Shelf and select the “Top N Values” and “Bottom N Values” check-box and hit “OK”.

10

11

tipsJust for better understanding how “Bottom N” Calculated Field is working. There really is no limit to the creative ways you can use calculated values and Tableau calculation to enhance the information.

10

Using sets is probably the preferred solution to the Top / Bottom problem. One reason is that unlike table calculation filters, members of a set are computed in the data source and, therefore, should be much more efficient. Although it won’t make a much of a difference with a few hundred data points.

METHOD 3

Step 1:

Right Click on “Customer Name” dimension present at the data pane section and then select “Create” -> “Set” Option.

13

Step 2:

Create a Set and name it as TOP N VALUE. Go to the Top filter tab, and define the parameter range value for the number of customers you want to display in the bar chart.

If you want a dynamic value rather than Static values, you can invoke the Parameter Control [Show N values]

14

Step 3:

Now that you have one set, it’s easy to create a second set for the bottom n values. Duplicate the existing Set and rename it to BOTTOM N VALUE and change value from “Top” to “Bottom” as described below.

15

Step 4:

Now you have two sets, one for the TOP N VALUE and one for the BOTTOM N VALUE. Ctrl-click both sets > right-click > Create Combined Set and select “All Members in Both Sets”

16

Step 5:

Now you can drag this new set [TOP & BOTTOM VALUES] to the Filter shelf and you can able to see your desired result.

17

 

 

 

 

 

 

 

Related Posts

About the Author: Rajeev Pandey

I am a multidisciplinary designer working in data visualization, interaction design and innovation. Expertise in developing Tableau, Web focus based visualization and reporting applications. I have a passion for analyzing, dissecting, and manipulating data sets as well as, building beautiful dashboard. Naturally talented in communicating between technology and business needs. Diverse and experienced in plenty of different domains .I am quick learner who can absorb new ideas and can communicate clearly and effectively.I love creativity and enjoy experimenting with various technologies.

Leave a comment

You must be logged into post a comment.