Tableau

Tableau Filtering Actions Made Easy

This is a guest post provided by Vishal Bagla, Chaitanya Sagar, and Saneesh Veetil of Perceptive Analytics.

Tableau is one of the most advanced visualization tools available on the market today. It is consistently ranked as a ‘Leader’ in Gartner’s Magic Quadrant. Tableau can process millions of rows of data and perform a multitude of complex calculations with ease. But sometimes analyzing large amounts of data can become tedious if not performed properly. Tableau provides many features that make our lives easier with respect to handling datasets big and small, which ultimately enables powerful visualizations.

Tableau’s filtering actions are useful because they create subsets of a larger dataset to enable data analysis at a more granular level. Filtering also aids user comprehension of data. Within Tableau data can be filtered at the data source level, sheet level or dashboard level. The application’s filtering capabilities enable data cleansing and can also increase processing efficiency. Furthermore, filtering aids with unnecessary data point removal and enables the creation of user defined date or value ranges. The best part is that all of these filtering capabilities can be accessed by dragging and dropping. Absolutely no coding or elaborate data science capabilities are required to use these features in Tableau.

In this article, we will touch upon the common filters available in Tableau and how they can be used to create different types of charts. After reading this article, you should be able to understand the following four filtering techniques in Tableau:

  1. Keep Only/Exclude Filters
  2. Dimension and Measure Filters
  3. Quick Filters
  4. Higher Level Filters

We will use the sample ‘Superstore’ dataset built in Tableau to understand these various functions.

1. Keep Only/Exclude Filters in Tableau

These filters are the easiest to use in Tableau. You can filter individual/multiple data points in a chart by simply selecting them and choosing the “Keep Only” or “Exclude” option. This type of filter is useful when you want to focus on a specific set of values or a specific region in a chart.

While using the default Superstore dataset within Tableau, if we want to analyze sales by geography, we’d arrive at the following chart.

1.png

However, if we want to keep or exclude data associated with Washington state, we can just select the “Washington” data point on the map. Tableau will then offer the user the option to “Keep Only” or “Exclude”. We can then simply choose the option that fits our need.

2.png

2. Dimension and Measure Filters

Dimension and measure filters are the most common filters used while working with Tableau. These filters enable analysis at the most granular level. Let’s examine the difference between a dimension filter and a measure filter.

Dimension filters are applied to data points which are categorical in nature (e.g. country names, customer names, patient names, products offered by a company, etc.). When using a dimension filter, we can individually select each of the values that we wish to include or exclude. Alternatively, we can identify a pattern for the values that we wish to filter.

Measure filters can be applied to data points which are quantitative in nature, (e.g. sales, units, etc.). For measure filters, we generally work with numerical functions such as sum, average, standard deviation, variance, minimum or maximum.

Let’s examine dimension filters using the default Tableau Superstore dataset. The chart below displays a list of customers and their respective sales.

3.png

Let’s examine how to exclude all customers whose names start with the letter ‘T’ and then subsequently keep only the top 5 customers by Sales from the remaining list.

One way would be to simply select all the customers whose names start with ‘T’ and then use the ‘Exclude’ option to filter out those customers. However, this is not a feasible approach when we have hundreds or thousands of customers. We will use a dimension filter to perform this task.

When you move the Customer Name field from the data pane to the filters pane, a dialogue box like the one shown below will appear.

4.png

As shown in the above dialogue box, you can select all the names starting with “T” and exclude them individually. The dialogue box should look like the one shown below.

5.png

The more efficient alternative is to go to the Wildcard tab in the dialogue box and select the “Exclude” check box. You can then choose the relevant option “Does not start with”.

6.png

To filter the top 5 customers by sales, right click on “Customer Name” in the Filters area, select “Edit Filter” and then go to the “Top” tab in the filter dialogue box. Next, choose the “By Field” option. Make your selections align to the following screenshot.

top-5-customers-by-sales-filter

After performing the necessary steps, the output will yield the top 5 customers by sales.

top 5 customers by sales

Let’s move on to measure filtering within the same Tableau Superstore dataset. We’re going to filter the months where 2016 sales were above $50,000. Without a measure filter applied, our sales data for 2016 would look like the following:

9.png

To filter out the months where sales were more than $50,000, move the sales measure from the data pane to the filter pane. Observe the following:

10.png

Here, we can choose any one of the filter options depending upon our requirement. Let’s choose sum and click on “Next”. As shown below, we are provided with four different options.

11.png

We can then choose one of the following filter options:

  • Enter a range of values;
  • Enter the minimum value that you want to display using the “At least” tab;
  • Enter the maximum value that you want to display using the “At most” tab;
  • From the Special tab, select “all values”, “null values” or “non-null” values;

Per our example, we want to filter for sales that total more than $50,000. Thus, we will choose the “At least” tab and enter a minimum value of 50,000.

12.png

In the output, we are left with the six months (i.e. March, May, September, October, November, December) that have a sum of sales that is greater than $50,000.

13.png

Similarly, we can choose other options such as minimum, maximum, standard deviation, variance, etc. for measure filters. Dimension and measure filters make it very easy to analyze our data. However, if the dataset is very large, measure filters can lead to slow performance since Tableau needs to analyze the entire dataset before it filters out the relevant values.

3. Quick Filters

Quick filters are radio buttons or check boxes that enable the selection of different categories or values that reside in a data field. These filters are very intuitive and infuse your visualizations with additional interactivity. Let’s review how to apply quick filters in our Tableau sheet.

In our scenario, we have sales data for different product segments and different regions from 2014 to 2019. Our data looks like the following:

14.png

We want to filter the data by segments and see data for only two segments (Consumer and Corporate). One way to do this would be to use a dimension filter, but what if we want to compare segments and change the segment every now and then? In this scenario, a quick filter would be a useful addition to the visualization. To add a quick filter, right click on the “Segment” dimension in Marks pane and choose “Show Filter”.

15.png

Once we click on “Show Filter”, a box will appear on the right side of the Tableau screen. The box contains all constituent values of the Segment dimension. At this point, we could choose to filter on any segment value available in the quick filter box. If we were to select both Consumer and Corporate values, Tableau will display two charts instead of three.

16

Similarly, we can add other quick filters for region, country, ship status or any other dimension.

17.png

4. Higher Level Filters

Dimension, measure and quick filters are very easy to use and make the process of analyzing data hassle free. However, when multiple filters are used on a large data source, processing becomes slow and inefficient. Application performance degrades with each additional filter.

The right way to begin working with a large data source is to initially filter when making a connection to the data. Once the data is filtered at this stage, any further analysis will be performed on the remaining data subset; in this manner, data processing is more efficient. These filters are called Macro filters or Higher-Level filters. Let’s apply a macro level filter on our main data source.

We can choose the “Add” option under the Filters tab in top right corner of the Data Source window.

18.png

Once we click on “Add”, Tableau opens a window which presents an option to add various filters.

19.png

Upon clicking “Add” in the Edit Data Source Filters dialogue box, we’re presented with the entire list of variables in the dataset. We can then add filters to the one we select. Let’s say we want to add a filter to the Region field and include only the Central and East region in our data.

20.png

Observe that, our dataset is filtered at the data source level. Only those data points where the region is either Central or East will be available for our analyses. Let’s turn our attention back to the sales forecast visualization that we used to understand quick filters.

21

 

In the above window, we observe options for only “Central” and “East” in the Region Filter pane. This means that our filter applied at the data source level was successful.

Hopefully after reading this article you are more aware of both the importance and variety of filters available in Tableau. However, using unnecessary filters in unorthodox ways can lead to performance degradation and impact overall productivity. Therefore, always assess if you’re adding unnecessary options to your charts and dashboards that have the potential to negatively impact performance.

Author Bio:

This article was contributed by Perceptive Analytics. Vishal Bagla, Chaitanya Sagar, and Saneesh Veetil contributed to this article.

Advertisements

Use Parameters in Tableau to Enhance Your Tables

When you receive a requirement to make a boring Excel style table in Tableau, consider spicing up the table by incorporating parameters. One clever use of parameters enables you to incorporate user defined rows and columns into a Tableau table layout. As a user selects a parameter value (representing a column or row), the table is dynamically updated to show the column or row that was selected.

“Parameters are useful when you want to add interactivity and flexibility to a report, or to experiment with what-if scenarios. Suppose you are unsure which fields to include in your view or which layout would work best for your viewers. You can incorporate parameters into your view to let viewers choose how they want to look at the data.

When you work with parameters, consider the following two things that are important in making them useful:

They need to be used in calculations.
The parameter control needs to be displayed so that viewers can interact with it.”

In this video I will show you how to infuse an otherwise boring table with some parameter driven interactivity. Enjoy!

Reference: Tableau Online Help

Benford’s Law Visualization in Tableau

Benford’s law, also called the first-digit law, is an observation about the frequency distribution of leading digits in sets of numerical data. The law states that in many naturally occurring collections of numbers, the leading significant digit is likely to be small [1]. For example, in sets that obey the law, the number 1 appears as the most significant digit about 30% of the time, and the percentages decrease all the way down to a leading digit of 9, which appears 4.6% of the time.

Why Run This Analysis?

When fraudsters are fabricating data, they may not know to create fake data that conforms to Benford’s Law.  Constructing a Benford’s Law visualization in Tableau can help you determine if your numerical data is fake or at least raise doubts about its authenticity.

In short, remember that one isn’t always the loneliest number!

If you’re interested in Business Intelligence & Tableau subscribe and check out all my videos either here on this site or on my Youtube channel.

[1] https://en.wikipedia.org/wiki/Benford%27s_law

 

Use Separate Legends in Tableau (Distinct Column Coloring)

Here’s a handy tip for Tableau 10.2 and above. Learn to create an individual color legend for each measure in the view and then assign a different color palette to each column. This was very difficult to do prior to Tableau 10.2 but now you can apply different color palettes to individual columns with ease!

If you need to know how to apply conditional color formatting to dimensional values watch this video: How to Conditionally Format Text Cell Color in Tableau

If you’re interested in Business Intelligence & Tableau subscribe and check out all my videos either here on this site or on my Youtube channel.

How to Conditionally Format Text Cell Color in Tableau

 

Even though Excel and Tableau are far from the same tool, sometimes you have to find a way to force Tableau to behave in an Excel-like manner. Conditionally changing the background color of text in Excel is very easy but requires a hack in Tableau 10.3. Use my video to learn how to conditionally format the cell background of a text or dimensional value in Tableau. Trust me, this is a time saver!

If you’re interested in Business Intelligence & Tableau subscribe and check out my videos either here on this site or on my Youtube channel.

Create a Map with Multiple Layers in Tableau

In this video you’ll learn how to create a map with multiple layers in Tableau using Tableau’s included superstore data set.

  1. We’ll start by building a filled map that represents the profit by state.
  2. We’ll layer on top of this map a pie chart that breaks down Sales by Category.
  3. As a bonus tip we’ll touch upon the FIXED Level of Detail (LOD) expression in order to calculate a percentage of sales by state and category for the pie chart.

If you’re interested in Business Intelligence & Tableau please subscribe and check out my videos either here on this site or on my Youtube channel.

Create a Gantt Chart in Tableau

 

Learn to create a Gantt Chart in Excel following the steps I laid out in the above video. In case your tool of choice is Excel, check out my other video on how to create a Gantt Chart in Excel. Your inner project manager will thank you!

If you’re interested in Business Intelligence & Tableau subscribe and check out my videos either here on this site or on my Youtube channel. You will be smarter for it!

 

Coursera Review: Creating Dashboards and Storytelling with Tableau

Discounts Harm Profits

I recently finished the “Creating Dashboards and Storytelling with Tableau” course on Coursera. The course was taught by adjunct faculty at the University of California Davis. Although it is the fourth course of five in the “Data Visualization with Tableau” specialization, it is only the third course that I have taken. I skipped the very basic first course and will concentrate next on finishing the capstone. 

If you do take this course be prepared to put in a fair amount of work on weeks three and four when the dashboard and story project are respectively due. I put in at least five hours of effort on each individual assignment not including watching videos, reading materials and taking quizzes.

I found the storytelling course to be informative and worthwhile. Unlike a Udemy course on Tableau that wades right into the applied aspects of clicking and dragging items, Coursera courses offer more of an academic background on the subject matter.

The point of this course is to hammer home that stories provide context and meaning that can’t be matched by a list of facts. We’re informed that stories engage more of your brain than simply absorbing a list of facts.

We learn that you should always try to make your stories relatable to the viewer so that they personally connect or identify with some aspect of the story. You should find a specific story of a person who exemplifies the larger narrative rather than starting with a lot of general facts and figures.

Politicians employ this tactic all of the time. Instead of spouting off a list of facts about their particular issue, the politician will first paint a picture regarding Joe the small businessman or Jill the single mom. They’ll then discuss how legislation (or lack thereof) will affect their constituents particular situations; in the hope that the listener will relate to the individuals. This is an exercise in using the particular to illuminate the general.

Here are a few of the tips I learned in regard to telling stories with data:

  • Use time based trends and consider a line or bar graph depending upon the data;
  • Use rank ordering (e.g. use a bar graph to rank salespersons by sales);
  • Use data comparisons where appropriate (e.g. polling data showing candidate support over a period of time);
  • Use counter intuitive visualizations (e.g. most people are surprised to learn that the United States has the highest incarceration rate by far amongst OECD countries);
  • Tell stories through relationships (e.g. use scatterplots to illustrate the relationship between sales and profits);
  • Check your facts;
  • Focus on a key statistic or intriguing piece of information;
  • Make your story insightful; don’t leave the audience guessing on what you want them to take away form your presentation;
  • Make your story relatable;

By all means check out my submission for the final project. I illustrated the relationship between discounted orders and profits to show that discounted orders are by far less profitable. This was accomplished by creating a set in Tableau to identify all discounted orders.

Until next class!

See also:

Coursera Final Assignment: Essential Design Principles for Tableau

Coursera Final Project: Data Visualization and Communication with Tableau

How to Build a Waterfall Chart in Tableau

In this video I will show you how to go “Chasing Waterfalls” in Tableau (apologies to TLC). Waterfall charts are ideal for demonstrating the journey between an initial value and an ending value. It is a visualization that breaks down the cumulative effect of positive and negative contributions. You’ve probably seen them used in financial statements or at your quarterly town hall meeting. Enjoy!

If you’re interested in Business Intelligence & Tableau please subscribe and check out my videos either here on this site or on my Youtube channel.

Coursera Final Assignment: Essential Design Principles for Tableau

Dashboard 1

I recently completed Essential Design Principles for Tableau offered by the University of California Davis on Coursera. I’ll offer some review commentary. I thought it was a solid class as it covered data visualization concepts such as pre-attentive attributes and the Gestalt principles. This class was a bit more heavy on the conceptual side of the house as opposed to delving into practical Tableau instructions. However, there are other classes in the specialization that have a more hands on practical approach.

In this assignment we had to highlight the three worst performing product Sub-Categories in each region. Additionally, we had to demonstrate how these worst performers compared to other product Sub-Categories in their respective regions. Finally, the visualization had to highlight the three worst performing Sub-Product Categories overall with a color emphasis. The scenario given to the class was that a sales manager had to cut the three worst performing Sub-Categories in her region and needed a visualization that addressed her concerns.

Guidance was not provided on how to identify the three worst performing categories. Some people in the class simply used profit as their key performance indicator (KPI) which I think is misguided. You learn in business (or business education) that profits do not equal profitability.  From Investopedia:

Profitability is closely related to profit, but it is the metric used to determine the scope of a company’s profit in relation to the size of the business. Profitability is a measurement of efficiency – and ultimately its success or failure. It is expressed as a relative, not an absolute, amount. Profitability can further be defined as the ability of a business to produce a return on an investment based on its resources in comparison with an alternative investment. Although a company can realize a profit, this does not necessarily mean that the company is profitable.

For these reasons I used the Average Profit Ratio of the products in each Sub-Category as my KPI as opposed to raw profits. If you had to sell $100,000 of product A to make $1,000 in profit (1% profit ratio), would you eliminate product B which requires $1000 in sales to generate $500 in profit (50% profit ratio)? Only if you want to go out of business!

In order to complete the visualization you see above on Tableau Public I had to incorporate nested sorting principles and also highlight the three worst performing elements on a bar chart. Luckily for you, I have videos that will demonstrate how to accomplish these tasks.

You can check out the rest of my videos on my Youtube Channel or find them on this site under Videos.