If you collect large sets of data that you want to visualize, 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.
This article describes how to create an interactive view that separates your customers into two dynamic groups:
- The top customers
- All other customers
The view includes a control that your users can adjust to change the number of customers included in the top customers group. When they change the number, the view updates accordingly.
Sets and supported data sources
The method described in this article for creating a view of the top customers uses the In/Out functionality of sets. Sets were introduced with Tableau 8. For live connections, the In/Out functionality requires a relational or multidimensional data source. If you use a file-based data source, such as a Microsoft Excel workbook or .csv file, you can take an extract with which you can create sets. The attached sample workbook uses the Sample - Superstore - English (Extract) data source that comes with Tableau 8.0 Desktop.
Create a Top N Customers view
Step 1
Open a new workbook and connect to the Sample - Superstore - English (Extract) data source.
You might want to download and open the attached sample workbook alongside your new one.
Step 2
Right-click Customer Name and select Create Set.
Step 3
For Name, type Top N Customers by Sales.
Step 4
On the Top tab, select By Field, and then select Sales from the field list and Sum from the aggregation list.
Step 5
For the N value—that is, the number of top customers you want to show—you can specify a static number or a dynamic parameter.
This exercise uses the sample workbook’s Top Customers parameter. The Top Customers parameter is defined as an integer with allowable values of 5–40, in increments of 5.
Step 6
Click OK.
Tip: If you used a parameter to determine the size of the set, be sure to right-click the parameter and select Show Parameter Control.
Step 7
Complete the following steps to create the initial view:
- From the Sets pane, drag Top N Customers by Sales to the Rows shelf.
- From the Dimensions pane, drag Customer Name to the Rows shelf, positioning it to the right of the set.
- From the Measures pane, drag Sales to Text on the Marks card.
- To see that the set is working, click the Descending Sort button
on the toolbar.
Step 8
In the Sets pane, right-click Top N Customers by Sales and click Create Calculated Field.
Step 9
In the Calculated Field dialog box, complete the following steps.
- For Name, type Subset Labels.
- In the Formula box, build the following formula to create dynamic labels for the customers in the set:
IF [Top N Customers by Sales]
THEN "Top " + str([Top Customers]) + " Customers"
ELSE "Others"
END
- Confirm that the formula is valid, and then click OK.
Step 10
From the Dimensions pane, drag Subset Labels to the Rows shelf, placing it between the Top N set and the Customer Name dimension.
Step 11
On the Rows shelf, right-click the Top N set and select Show Header.
This hides the In and Out labels while retaining the sort order, so that your Top N subset always appears at the top of the view.
Additional tips for improving the view’s functionality
Here are some additional, optional steps you can take to give your viewers more flexibility in displaying the customer subsets.
Step 1
Select Analysis > Create Calculated Field.
Step 2
Complete the following steps to create a new parameter that you’ll use in the calculation.
- In the Calculated Field dialog box, at the top of the Parameters list, click Create.
- In the Create Parameter dialog box, create the following parameter:
- For Name, type Expand or Collapse.
- For Data type, select String.
- For Allowable Values, select List.
- In the List of values, add the values Expand and Collapse.
- Click OK.
Step 3
In the Calculated Field dialog box, complete the steps below to create a calculation that uses the parameter you created to enable viewers to specify how to view customers in the Others subset.
- For Name, type Customer Names.
- In the Formula box, build the following formula:
IF [Expand or Collapse]="Collapse" THEN
IF [Top N Customers by Sales]
THEN [Customer Name]
ELSE "Others" END
ELSE [Customer Name] END
- Confirm that the formula is valid, and then click OK.
Step 4
In the Parameters pane, right-click Expand or Collapse and select Show Parameter Control.
Step 5
From the Dimensions pane, drag Customer Names to the Rows shelf, and place it directly on top of Customer Name, so that it replaces Customer Name.
Now you can use the Expand or Collapse parameter control to see the names the Top N customers—where N is determined by the value set in the Top Customers parameter control—in one group, and the remaining customers individually or rolled up into a single Others entry.
