Tableau – Sets and Hierarchy functionality

Posted by Ankit Singhal

Business Intelligence and visual data analysis are core requirements of today’s businesses. While there are numerous tools with such capabilities, Tableau is among the most preferred BI tool for its added features such as interactive and shareable dashboard with graphical and chart representation of data. It also helps in connecting files, relational and Big Data sources to obtain relevant data for blending and real-time collaboration. Such range of features make Tableau a perfect data visualization tool to complete the multitude real-time business requirements that keep coming up. We are going to discuss one such requirement in this blog and see how Tableau helps solve it.

Business Problem

The business requirement is to show:

1. Top N customers and rest of the customers grouped as ‘Others’.
2. The customers grouped as ‘Others’ should always be displayed at the bottom of the table.
3. User should be able to select the number of customers to display.
4. In drill down, the user should be able to see the respective customers in ‘others’ grouping.

Our Solution

The requirement could be solved by using Sets and Hierarchy functionality in Tableau. Below is the detailed guide to get the results. We used ‘Superstore’ data to pursue this.

Step 1
We create a parameter ‘ENTER VALUE’ to provide dynamic selection for the user to choose the number of customers to display.

Step 2
Create a set on the Customer Name field to have Top N values.
• Right-click on the Customer Name field and select ‘Create Set’
• Select ‘Use all’ option in the General tab and then select the ‘Top’ tab.
• In ‘Top’ tab, select ‘By Field’ option and give the measure field on basis of which we will create the set. In our case, we are using ‘Sum of Sales’.
• Pass the parameter ‘ENTER VALUE’ in the place of the enter value option to make it dynamic and give the name of the set as ‘TOP’.

Step 3
Create another set on the Customer Name field to have all the values.
• Right-click on the Customer Name field and select create set.
• Select ‘Use all’ option in the General tab.
• Save the set as ‘ALL’.

Step 4
Create a combined set Others.
• In this combined set, set the criteria as select (ALL set) with except shared members. This will give the all the members who are in the ‘ALL’ set but not in ‘TOP’ set; hence, this combined set would provide the customer names grouped as others.

Step 5
Create Calculation fields and Hierarchy.
• First, create Calculation field ‘TOP and Others’ using the below syntax.

If ([Top]) then [Customer Name] else “Others” end

• Second, create Calculation field ‘Others Customers’ using the below syntax.

If ([Others]) then [Customer Name] else ” ” end

• Now, create the Hierarchy ‘TOP Customers and Others Customers’ and add the above two Calculation fields.

• Third, create Calculation field ‘Sort’ on ‘TOP and Others’ using the below syntax to sort the ‘Others Customers’ and displayed at the bottom.
Case [TOP and Others] when “Others” then “ZZZZZ” else “AAA” end

Step 6
Create View

• Drag and drop the created HierarchyTOP Customers and Others Customers’ into rows shelf and ‘sales’ to text shelf.
• Right-click on the Hierarchy field and select ‘sort’.
• In the ‘Sort by’ option, choose ‘Field’ and in the dropdown select Calculation field as ‘Sort’ and select aggregation to be ‘Minimum’.

• The View will now show the TOP N and Others but when we drill down by selecting the [+] button then it will show the customers in the ‘Others’ set too.

• Right-click on parameter ‘ENTER VALUE’ and select ‘show parameter control’. It will appear in right side of the View.
• User may now alter the number of customers to be shown in the view and the rest of the customers will be grouped in ‘Others’ and would be visible on clicking the [+] button.

With that, we solve the business problem of displaying the Top N customers along with the rest of the customers grouped as ‘Others’. As we see, Tableau provides several functionalities to cater to the varied needs of the users. Sets and Hierarchy functionality in Tableau help filter out relative data from the huge volume available in the database. A hierarchy helps organize different dimensions into an expandable structure within the view, while sets inside a hierarchy allow a more customized way to drill down through data.

Try using these functionalities and let us know your experience in the comments below.

Until next time!


Related Posts

  • Show/Hide Filter in TableauShow/Hide Filter in Tableau

    Despite the presence of several quick filters, legends, and other links, we may sometimes require more space to display data in the Tableau Dashboard. There’s, however, a restriction on the…

  • Formatting Individual Columns in Tableau (Dual-Axis Crosstabs)Formatting Individual Columns in Tableau (Dual-Axis Crosstabs)

    A simple step by step explanation with screenshots attached! Tableau can only format based off one value.  For example, you can have a chart that displays Sales and Profit, but…

  • Teradata Intelligent Memory (TIM)

    Overview of Teradata Teradata is a RDBMS (Relational Database Management System). This system is solely based on off-the-shelf (readymade) symmetric multiprocessing (SMP) technology combined with communication networking, connecting SMP systems…

  • Understanding Teradata Wallet

    Teradata Wallet is a facility for storage of sensitive/secret information, such as Teradata Database user passwords. Users are able to save and retrieve items by using this facility. Teradata wallet…

  • Real Time Data Ingestion (DiP) – Apache Apex (co-dev opportunity)Real Time Data Ingestion (DiP) – Apache Apex (co-dev opportunity)

    Data Ingestion Platform This work is based on Xavient co-dev initiative where your engineers can start working with our team to contribute and build your own platform to ingest any…

  • Manager’s Dilema: SAS vs R vs Python

    There are countless articles on this topic already, and I must begin by accepting that I am quite late to this superstar battle. However, every time these champions of analytics…

Leave a Reply

Your email address will not be published. Required fields are marked *