Filter Tableau Data By Wildcard Search With Multiple Keywords

In this video I will teach you how to perform a multiple keyword search in Tableau. I’ll show you how to perform both an OR search & an AND search against keywords you type into your dashboard parameter.

In order to accomplish these tasks, we need to use regular expressions. As I mention in the video, regular expressions can be indecipherable, akin to hieroglyphics or something out of a Dan Brown novel to the average person. You can go find this information on the Tableau knowledge base, but I want you to be the person that understands WHY something works as opposed to just copying and pasting a solution that you cannot explain to yourself or others.

The use case for multiple keyword search is for those times when you need to filter your data by a comment, description or other free form blocks of text that are not necessarily subject to the most stringent data governance rules, because let’s face it, these types of fields exist in abundance. In the video I create a dashboard that illustrates the power that this type of search can bring to your dashboard.

You can interact with the dashboard from the video on my Tableau Public page.

OR SEARCH

1. CREATE PARAMETER

Create a parameter named Search Terms.

  • Data type: String
  • Current value: keep it blank.
  • Allowable values: All

2. CREATE CALCULATED FIELD 1

Create a calculated field named Regex String (OR) including the following formula:

"(" + REPLACE([Search Terms], ',' ,'|') + ")"

Let’s breakdown what is going on with the apparent gibberish seen above (there is a method to the madness here). I’ll assume you want to perform a search against a field that contains the terms “paper” OR “Paper”; the case of the spelling is a factor in the results (case sensitive). To adhere to proper regex formatting, you could write a valid expression as such:

(paper|Paper)

This pattern indicates that you want to return a match on the characters “paper” OR “Paper” literally. You can test this out at https://regex101.com/

Although for some reason the official Tableau knowledge base article says to use the REGEX_REPLACE formula in lieu of REGEX, it is not needed since we are doing a simple replace and not using a regex formula to aid our replacements.

In the Tableau calculated field above, we are using the REPLACE function against the parameter named “[Search Terms]” to replace every instance of a comma with the the ‘|’ pipe symbol.

Think about it, if you type the terms “paper, Paper” into the a dashboard’s search term parameter as seen below, the calculated field will reformat your list into the proper regex format by replacing the comma with a pipe and then enclosing the term within parenthesis; i.e., (paper|Paper).

3. CREATE CALCULATED FIELD 2

Next create a calculated field named Regex OR Filter that contains the following formula:

REGEXP_MATCH([Product Name],[Regex String (OR)])

Let’s breakdown what is going on with the formula seen above. The REGEXP_MATCH formula is evaluating a properly formatted regex expression which is contained within the [Regex String (OR)] calculated field we created in step 2. This calculated field will contain the formatted expression (paper|Paper).

REGEXP_MATCH returns TRUE if a substring of the specified string matches the regex pattern. In our case

[Regex String (OR)] = (paper|Paper)


Thus the REGEXP_MATCH function will evaluate the regex expression (paper|Paper) from the second argument against the [Product Name] field in the first argument. The function will return either TRUE or FALSE depending upon whether a match is located within this field.

3. DRAG FIELDS TO ROWS AND FILTERS SHELF

Place [Product Name] to rows and [Regex OR Filter] to filters, then select True.

AND SEARCH

In order to create the AND search for your dashboard, you will follow very similar steps to the OR search. 1. Create the same parameter named Search Terms as in the OR Search above.

2. CREATE CALCULATED FIELD 1

Next create a calculated field named Regex AND Filter including the following formula:

"(?=.*" + REPLACE([Search Terms], ',' ,")(?=.*") + ").*"

This may look like something out of a Dan Brown novel, but don’t let it intimidate you. In a similar fashion to the OR search where we replaced commas with a pipe symbol, we are simply replacing commas with the following characters between the double quotes “)(?=.*”

The proper regex pattern to indicate an AND search against the terms paper and Paper within a body of text would be as such:

(?=.paper)(?=.Paper).*

Knowing the proper regex format should make the contents of the calculated field above more clear. We are using the REPLACE function against the parameter named [Search Terms] to replace every instance of a comma with the the )(?=.*symbols. Thus “Paper, paper” values typed into the [Search Terms] parameter becomes reformatted into(?=.paper)(?=.Paper).*

Placing .* at the end of the string means that any combination of characters can be placed after the search terms and still yield a TRUE result.

3. CREATE CALCULATED FIELD 2

Next create a calculated field named Regex AND Filter including the following formula:

REGEXP_MATCH([Product Name],[Regex String (AND)])

4. DRAG FIELDS TO ROWS AND FILTERS SHELF

Then place [Product Name] to rows and [Regex OR Filter] to filters, then select True.

CONCLUSION

That wasn’t so horrible was it? If you’re a visual learner like me, then go ahead and watch the video to hammer home the concepts. Hopefully this post will help you understand how these regex patterns work and how they can add value to your next dashboard design! As always if you learned something drop a comment on the YouTube video and let me know.

Please like and subscribe on the Anthony B. Smoak YouTube channel.

All views and opinions are solely my own and do not necessarily reflect those of my employer

I appreciate everyone who has supported this blog and my YouTube channel via merch. Please click here

Thank you!!

Anthony B Smoak

TLDR: Tableau Knowledge Base Reference: https://kb.tableau.com/articles/howto/how-to-filter-data-by-wildcard-search-with-multiple-keywords

Build an Interactive Tableau Resume to Get Noticed

Interact with my resume here: Anthony Smoak Interactive Tableau Public Resume

This post is for everyone who has ever asked, “How do you create an interactive resume in Tableau?” If you’re looking to get noticed as a Tableau visualization developer / subject matter expect, then building out an interactive resume using Tableau is a great place to start. It is a good starter visualization to build because you already have all the data! The data is inherently about you, but you just need some inspiration on how to get started building out your resume.

That’s where I come in, to share with you a place to start for inspiration, and direction on how to build out a few charts I leveraged to construct my interactive resume.

Where to Get Inspired

The first place you want to start looking for interactive Tableau resume inspiration is the Tableau Public Resume Gallery. The Tableau community has done an excellent job of sharing resumes so you don’t have to start from scratch with respect to idea generation. As I was looking through the gallery, I started to notice that most of the resumes had some common DNA, most notably a timeline chart (either linear or Gantt chart) and an abacas chart (both of which I will show you how to build in the video).

My resume is inspired by offerings from both Ann Jackson and Lindsay Betzendahl. When you look through the gallery you may find a resume that fits your personal vision. I’m sure you’ll use a few of the techniques I’m going to share as well.

You can also do a simple google image search on “Tableau Interactive Resume” and you’ll find additional images that link to resources that are not on the official Tableau Public Resume Gallery.

Format Your Data

In order to build out the necessary charts to support your resume, you’ll use Excel to format the data. I cover the format for both the timeline chart and the abacas chart in the video, but below is a sneak peek of how I formatted my data for the abacas skills chart.

The data above supports the buildout for two separate abacas charts. One chart is for a self-rating of my skills (rows 2-6), while the other chart is a representation of LinkedIn endorsements (7-11).

Column E represents the maximum value of the skill that I want to present on the abacas chart. Since I was self-rating my skills from a scale of 1 to 5, then 5 was the maximum value represented. For the LinkedIn endorsements, 51 was the highest number I received, thus for all rows supporting this chart, 51 was the maximum value.

Gantt Chart

Every resume needs a timeline! Although I describe how to build a single linear timeline chart in the video above, I have another video that explains how to build a Gannt chart in Tableau. For some of our experiences, we have more than one activity happening at the same time, thus the following video will help in your resume build out as well.

Remember that we all have a story to tell and an interactive resume in Tableau will help you share your experiences and get noticed if done well. Good luck!

Please like and subscribe on the Anthony B. Smoak YouTube channel.

All views and opinions are solely my own and do not necessarily reflect those of my employer

I appreciate everyone who has supported this blog and my YouTube channel via merch. Please click here

Thank you!!

Anthony B Smoak

INCREASE YOUR FOCUS TRY BRAIN.FM

When I am focusing hard during the day at work or developing visualizations on the weekend. I use Brain.fm to help me focus when it matters. It’s Science-driven and research-backed functional music designed from the ground up to help you you focus, relax, meditate and sleep. If you’d like to try a free trial check out https://brain.fm/anthonyb

Please use coupon code anthonyb for a 20% discount upon checkout. It helps support this blog and my YouTube channel as I receive a small commission for purchases made through links in this post.

Fix the Reset All Filters Button in Tableau

Let’s fix the “Reset All Filters” button in Tableau! Sometimes when we create the Reset All Filters button in Tableau, it doesn’t initially work. In this video and post I will troubleshoot a few of the reasons for the lack of functionality and get you and your dashboard up and running.

If you need a refresher on how to reset all filters in Tableau, make sure to check out this video first.

The inspiration for this post comes from the following dashboard I built for the Real World Fake Data (#RWFD) initiative spearheaded by Mark Bradbourne at Tableau. Mark was kind enough to include my dashboard on his recap of top submissions for this particular challenge. You can interact with the dashboard on my Tableau Public gallery here.

The dashboard above utilizes the reset all filters technique, but I initially ran into an issue when trying to get the technique to work. Here is the first area you should check if you run into issues.

SOURCE SHEETS / TARGET SHEETS

On your dashboard, Select Actions (or CTRL+SHIFT+D), and then edit your Reset All Filters action.

Once you select [Edit] for the dashboard action, make sure that your Reset Filters action is the only selection for your [Source Sheets]. Also make sure that all other sheets on the dashboard that you want to remove filters from are selected on [Target Sheets] EXCEPT for your Reset Filters action. See the figure below.

TARGET FILTERS

Additionally, double check to make sure that you have all of your necessary fields selected in the [Target Filters] section that you want to remove as a filter. I have found that selecting the [All Fields] option never works. I’ll repeat, double check that every field you want to reset appears as a target filter. If it is not there, then simply add it by selecting [Add Filter].

In my my particular circumstance, I did have all of the fields selected in the [Target Filters] section that were required to reset all filters, so I had to keep looking for answers. Let’s move to step 3 in the process.

VISUAL CUES FOR FILTERS

In your workbook, I want you to hunt down any filters that are applied to ALL WORKSHEETS with the same data source. From the Tableau knowledge base, here is a screenshot of the icons applied to fields on the Filters shelf. On your worksheets, look for the cylindrical database icon next to any filters. We will further investigate these filters.

Where you see the cylinder next to any Filters on your worksheets, change the [Apply to Worksheets] option from [All Using This Data Source] to [Selected Worksheets…]. In my case, the offending icon was next to the [Location City] filter.

This next step is key. Make sure to UNCHECK the Reset Filters Worksheet. Your filter should not be applied to the same worksheet that is used to display the Reset All Filters button.

Once you’ve unchecked the Reset Filters worksheet your icon next to the filter will change to the following.

Go back to your dashboard and test to make sure that your reset all filters button functionality works. In my case, the above trick was successful for me. I’m sure it will be for you as well.

Make sure to watch the video below as I step through the checks.

Please like and subscribe on the Anthony B. Smoak YouTube channel.

All views and opinions are solely my own and do not necessarily reflect those of my employer

INCREASE YOUR FOCUS TRY BRAIN.FM

When I am focusing hard during the day at work or developing visualizations on the weekend. I use Brain.fm to help me focus when it matters. It’s Science-driven and research-backed functional music designed from the ground up to help you you focus, relax, meditate and sleep. If you’d like to try a free trial check out https://brain.fm/anthonyb

Please use coupon code anthonyb for a 20% discount upon checkout. It helps support this blog and my YouTube channel as I receive a small commission for purchases made through links in this post.

Do Great Things with Your Data!

Place Bar Chart Labels Above Bars in Tableau

In this video I will provide a method in which you can place your bar chart labels above the bars in Tableau. This technique is based off Adolfo Hernandez’s technique with a little more explanation and additional alternatives for the zero line. Make sure to add this to your bar chart repertoire!

If you want to follow-along with the video, you can download the data at this link:

https://www.basketball-reference.com/leagues/NBA_2021.html

In the video I exported data from the “Per Game Stats” section but you are free to download from whichever stats section you’d prefer.

High Level Steps:

Make sure to watch the video to get more in-depth:

  1. Drag your measure to columns (e.g., 3P)
  2. Drag your dimension to Rows (e.g., Team)
  3. Sort the measure to descending value
  4. Place the Team and 3P on the label and format accordingly
  5. Create a placeholder directly on the Columns shelf : AVG(0))
  6. Drag the placeholder to the bottom of the visual onto the 3P axis to make a combined axis visual
  7. In the Measure Value area make sure that AGG(AVG(0)) is placed above SUM(3P)
  8. Uncheck “Show Headers” for the Team dimension and for the axis at the bottom of the visual
  9. Format the visual to remove unnecessary borders and lines
  10. Make the following adjustments on the Label:
    1. Marks to Label: Min/Max
    2. Scope: Pane
    3. Field: Measure Values
    4. Options:
      1. Allow Labels to Overlap Other Mark
      2. Label Minimum Value
  11. Change the color of the bar chart borders to the background color of your visual in order to camouflage the tiny bar created by AVG(0)
  12. Uncheck “Show Header” for AVG(0)

When it’s all said and done you have a fresh new take on adding labels to your bar chart that can add differentiation to your overall visual!

For more detail on how to add a zero line (which I believe is important for bar charts in general and is a good design practice), watch the video.

Please like and subscribe on the Anthony B. Smoak YouTube channel.

All views and opinions are solely my own and do not necessarily reflect those of my employer

Do Great Things with Your Data!

Stacked Bar Chart with Dynamic Totals in Tableau

Are you looking for the next viz to showcase on your Tableau Public page? In this video I will teach you a technique that spices up the humble stacked bar chart with dynamic totals (using Tableau Set Actions). We will build out the viz step by step with Superstore data.

Stacked Bar Chart with Dynamic Totals

This chart is powered by Tableau Superstore data which is a data set that is readily available on the internet and is packaged as the default data set with Tableau. As you select the three legend categories at the top of the visual, the stacked bar chart sections will appear or disappear. The totals will also automatically update based upon your selection.

How cool is that!!??

I have to give a shoutout to Dorian Banutoiu for originating this technique. A few years ago, Dorian used this technique in a Makeover Monday exercise and it recently caught my attention when I was checking out his Tableau Public page. I immediately attempted to reverse engineer the technique (which admittedly took some effort).

Because I wanted to enable everyone with Tableau and/or Tableau Public to duplicate the chart, I used Tableau Superstore data as my foundation. Make sure to give Dorian a Twitter follow at @canonicalizedco.

What’s In it for You?

By following along with the video, you will utilize multiple Tableau elements such as:

Practice makes perfect so this will be a good opportunity for you to practice multiple Tableau elements with the creation of one visualization. You can click the links on the list above to see additional videos that cover respective areas.

Give Credit

If you do reproduce this visualization step by step or leverage the technique for your Tableau Public page or Linkedin Page, please link to this post or the YouTube video and place “Inspired by Dorian Banutoiu & Anthony Smoak” somewhere on the viz and post text.

Interact with the Finished Visualization

You can interact with the finished visualization on my Tableau Public page here:

https://tabsoft.co/3oNxq5Z


Please like and subscribe on the Anthony B. Smoak YouTube channel.

All views and opinions are solely my own and do not necessarily reflect those of my employer

Do Great Things with Your Data!

★☆★ Support this Channel: ★☆★

Merch ► shop.spreadshirt.com/AnthonySmoak

★☆★ FOLLOW ME BELOW: ★☆★

Twitter ► https://twitter.com/AnthonySmoak

Facebook ► https://www.facebook.com/AnthonyBSmoak/

Tableau Public ►Search for “Anthony B. Smoak”

Add Total Values for Stacked Column and Stacked Bar Charts in Excel

This is the only video you need to learn how to add add totals to stacked bar charts and stacked column charts in Excel. Make sure to watch the video because I have you covered both ways.

I’m more of a video explanation person, so make sure to watch the video so the steps are clearer. Then refer to the bulleted steps as reference once you have the initial basic understanding.

The Keys to Adding Totals to the Stacked Column Chart Above:

  • Add a “Grand Total” column to your data
  • Highlight your data not including the “Grand Total” column
  • On the “Insert” menu select a “2-D Stacked Column” chart
  • Select “Switch Row/Column” as necessary so your first data column is listed on the X axis
  • Select the chart and then expand the data range to include the “Grand Total” column
    • Add “Data Labels” to the Grand Total series on the chart
  • Right click on the Grand Total and “Change Series Chart Type”, the viz becomes a “Combo” chart
  • Change the “Grand Total” series chart type to a “Line”, while leaving all others as a “Stacked Column”
  • Format the line’s data label, changing the Label Position to “Above”
  • Select the line, format the data series and change the “Line” option to “No Line” in order to leave only the totals and hide the line.

The Keys to Adding Totals to the Stacked Bar Chart Above:

  • Add both “Grand Total” and “Spacing” columns to your data
    • Highlight your data including the “Spacing” column but not including the “Grand Total” column
      • The “Spacing” column should have a value of 0 at this point
    • On the “Insert” menu select a “2-D Stacked Bar Chart”
    • Select “Switch Row/Column” as necessary so the “Spacing” values are not listed as an option on the Y axis
    • Change the “Spacing” column values to a number (e.g., 1000) big enough to make a new category visible on the stacked bar chart
    • Right click to “Format Data Labels” and change the “Label Options” to “Value from Cells”
      • In the “Select Data Label Range” pop up box, highlight the values from the “Grand Total” column
      • Change the “Label Position” to “Inside Base”
    • On the chart select the Grand Total Series and right click so the “Fill” option appears
      • Change the “Fill” to “No Fill”
    • Change the values in the “Spacing” column to 0
    • Delete the “Grand Total” entry from the Legend

You can thank me by liking and subscribing to my YouTube Channel

All views and opinions are solely my own and do NOT necessarily reflect those of my employer.

Do Great Things with Your Data

– Anthony B. Smoak

shop.spreadshirt.com/AnthonySmoak

INCREASE YOUR FOCUS TRY BRAIN.FM

When I am focusing hard during the day at work or developing visualizations on the weekend. I use Brain.fm to help me focus when it matters. It’s Science-driven and research-backed functional music designed from the ground up to help you you focus, relax, meditate and sleep. If you’d like to try a free trial check out https://brain.fm/anthonyb

Please use coupon code anthonyb for a 20% discount upon checkout. It helps support this blog and my YouTube channel as I receive a small commission for purchases made through links in this post.

How to Filter your Tableau Viz in Tooltip (Top 10 Values)

Visualizations in tooltips, affectionately know as “Viz in tooltip” is a handy feature available in Tableau that enables “details on demand” functionality. As the user hovers over a specific mark or data point, additional details are revealed that are filtered specifically for that mark from another worksheet.

In the example above, as the user hovers over a bar, they obtain additional details about the three most profitable products associated with the respective bar.

As I learned in a very informative Tableau presentation for tooltip wonks (myself included), the underlying architecture is built upon action commands and shares many commonalities with action filters. For viz in tooltip performance considerations, use smaller and fewer visualizations. Also try to avoid maps and other complex visualizations that have significant mark density.

If your tooltip responsiveness is greater than 2 seconds or the height and or width is greater than 600 pixels, then consider rethinking your approach. According to Tableau best practice, users are not willing to wait more than 2 seconds hovering over a mark for a reveal.

Since the viz in tool tip passes filters between worksheets, this means we can make use of context filters (click this link for a fantastic overview) to limit the number of marks returned and help improve performance.

This is the Section You are Here for

Context filters also help solve the problem of returning the Top N records associated with a mark. When you assign a viz in tooltip on your source sheet, a set filter is applied on the target (i.e., viz in tooltip) worksheet. If you’re a frequent watcher of my videos you know that the Tableau order of operations prevents the default set filter from returning a proper Top N.

By adding the set filter to the context on the Order of Operations skyscraper, the data is pre-filtered by your dimension first (e.g., State) and then the Top N filter is applied. When the set filter turns gray, you know it’s working.

Notice that the Context Filter box is above the Sets entry; which means that the Context filter is evaluated BEFORE the set. Make sure to watch the video to learn how to limit to the Top 10 cities based upon a hovered state.

Check out the video for details and may all your viz in tooltips be context appropriate!

All views and opinions are solely my own and do NOT necessarily reflect those of my employer.

Do Great Things With Your Data

-Anthony B. Smoak

Create Tableau KPIs Quick and Easy (Profit vs Budget)

In this video you will learn a quick and easy approach to putting together KPIs that illustrate how profits perform in relation to a budget (i.e., targets vs actuals). Of course the KPIs will be interactive thanks to help from parameters.

Now the dashboard that I built around the KPIs will definitely form the basis of additional videos. There are several techniques here (filtering viz in tooltips, show/hide container, bar in bar chart, reset all filters button, etc.) that form the basis of a good intermediate level dashboard.

Inspiration comes in many forms. I have to give a shoutout to Keith Dykstra for offering his original dashboard for reverse engineering. The idea for the KPIs and bar in bar chart are inspired by Keith. I added additional elements such as the reset all filters button, filtering by states via treemap, parameters in lieu of hover over images.

Shoutout to the Kevin Flerlage for his great PowerPoint button workbook. I modified the on/off toggle button based upon a template from Kevin’s workbook.

Finally, I was watching an Oregon vs UCLA football game one Saturday and was impressed by Oregon’s latest uniform combination. I had to throw that grey and green combination together for use on the dashboard. Inspiration can come from many places! Here’s a little Oregon football and my “Saturday Night Lab” tweet.

Make sure you watch this video to learn and hopefully get inspired yourself!

If you’re interested in KPIs you can check out these other videos:

All views and opinions are solely my own and do NOT necessarily reflect those of my employer.

Tableau Bar Chart: Combining Small Values

In this video we’ll learn how to build a bar chart visualization that combines values below a certain threshold into an “OTHER” bucket. This technique is very useful when limiting the number of bars to show on your visualization while not losing sight of all the smaller values.

  • Using standard Tableau Superstore data, we’ll calculate the percentage of sales that are generated by each individual state.
  • We’ll then use a parameter to set a percentage threshold where all states below this percentage will be combined.
  • This technique also requires the use of sets and Fixed LODs.

This technique allows us to combine all states below a certain threshold (e.g., 2%) into one single bar chart showing a combined 24%.

Bar Chart Below Threshold Thumb 01

I have to give credit where credit is due to Ann Jackson for sharing this technique at TC19!

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

All views and opinions are solely my own and do NOT necessarily reflect those of my employer.

Build a Stacked Donut Chart in Tableau

Have you ever wanted to stack 4 pie charts on top of each other to build a visual? Let’s have some fun building out a stacked donut chart or a “TrailBlazer” chart as I call it due to its likeness to a particular NBA team’s logo.

Stacked Donut Chart Thumb

Portland Trailblazers anyone?

Stacked Donut vs Trailblazer

In order to build out this chart I used an innovative technique shared by Simon Runc on the Tableau forums. Feel free to check out that post here.

Using Tableau’s Sample Superstore Data, Simon came up with an innovative use of the INDEX() function and the Size functionality to create three different pie charts that each show a respective percentage of a measure (in this case Sales) to the Total amount of the measure.

For example, the chart highlights in red the percentage of Consumer Sales as a percentage of all segment sales (i.e., Consumer + Corporate + Home Office). The grey portions represent all other sales, other than the segment of interest.

The trick to this approach is using the Index function to create a pie chart per segment. For example Consumer is assigned a value of 1, Corporate a value of 2 and Home Office is assigned 3. When the INDEX() value is placed on size, the three different charts are assigned sizes where one is slightly larger than the next.

Stacked Donut Raw 2

With a little division and axis customization, the three segments are placed on top of each other to provide a stacked pie chart effect. The hole is courtesy of the standard methodology for creating a donut chart which involves a dual axis.

It makes much more sense when you see it in action so make sure to watch the video!

Here is an example of the raw stacked donut chart before the “TrailBlazer” formatting.

Stacked Donut Raw

Feel free to interact with the viz and download the workbook on Tableau public:

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

Definitely check out other posts of interest for building donut charts in Tableau:

All views and opinions are solely my own and do NOT necessarily reflect those of my employer.