How to Swap Sheets in Tableau

Learn how to perform a useful Tableau hack that allows you to display multiple sheets in one container on your Tableau dashboard. In this video I use my personal training dashboard to show you step by step how this trick is performed. This tip is a must know for the intermediate to advanced dashboard builder as it will help you save space on your dashboard.

Learn more about the dashboard used in this video: https://youtu.be/MFluvSKJXnI

Interact with the Dashboard here:

Watching the video will make the concept clearer but I will provide an overview in this post.

Step 1: I create a Parameter named “Select a Chart”. You can see that I have chosen a list of allowable values and I place into the list the names of charts that I want to swap.

Step 2: I create a calculated field named “_Selected Chart”. It only holds the value of the parameter I created in Step 1.

Step 3: (Use screenshot below)

1. Place the “_Selected Chart” calculated field on the filter shelf of a chart that you wish to show and hide.

2. Edit the “_Selected Chart” filter and select the “Custom value list” option.

3. Type in the respective name of the chart that corresponds to the value that you entered in the parameters allowable values list in Step 1. Hit the plus button to the far right to add the value. Additionally add the value of “All” to the Custom value list in the same manner.

IMPORTANT: the value that you enter into your chart must match EXACTLY to the value that you placed on the parameter allowable values list.

Repeat this process for every chart that you wish to show and hide, making sure to type in the exact same chart name that you entered in the parameter allowable values list in Step 1.

Step 4:

Now it’s time to place all of your charts into the same object (i.e., horizontal or vertical container) on your dashboard . Make sure to show the parameter named “Select a Chart” on the dashboard so you have a combo box with the names of your charts inside that you can select.

Make sure to watch the video for exact details!!

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

Advertisement

How to Become a Data Analyst

I’ve been working with data for some 20 plus years as of the writing of this post. In the video below I captured my thoughts on the required hard and soft skills it takes to succeed as a data analyst. If you are looking to start your career in data as someone who has not yet graduated or as someone with tangential work experience, then this video will serve you well.

Do You Need a Computer Science Degree to be a Data Analyst?

This question is frequently asked by people such as yourself looking to make a move into data. The answer is no. You do not need a computer science degree to have a very successful data career. In the video I give my thoughts on computer science, but the reality is that although it may be helpful from a “getting a first job” perspective, it is not a requirement to succeed. Although I have an undergraduate computer science degree from Clark Atlanta University (shout-out to HBCU alums), some of the brightest minds I’ve worked with in the data space do not have a computer science degree. Bottom line; a formal computer science degree certainly helps but it is by no means necessary. All you need is the willingness to learn the tools and the perseverance to get your first data opportunity.

Hard Skills Required (View Video)

I’ll give you a hint, data visualization skills are a must and Tableau is the tool of choice for me.

Soft Skills Required

I’ll keep it short here and simply state that you should always look for ways to differentiate yourself and not just be seen as an interchangeable commodity worker. To paraphrase famed Harvard professor Michael Porter, a differentiation strategy advocates that a business must offer products or services that are valuable and unique to buyers above and beyond a low price. In this metaphor, think of yourself as a business and you bring multiple skill sets to your employer (other than being a single focus technical employee who can be easily outsourced for a lower price).

To be a differentiator, do not think of yourself as just being a tool specific analyst. Learn how to take requirements, communicate well, develop exceptional writing skills for business emails and documentation. Finally, learn how to present your analyses to people several pay grades above yourself when required. You want differentiation to be your competitive advantage. You do not want “low cost” to be your advantage for obvious reasons (if you’re like me, you want to be paid fairly for the value you provide).

Future Career Paths

In our jobs we desire mastery, autonomy and purpose. After a certain point in your career you may want to take a leap from the descriptive analytics path and move towards a predictive analytics path. Descriptive analytics (think data analyst or traditional business intelligence reporting analyst) deal with what has happened in the past while predictive analytics focus on what will most likely happen in the future. In order to level up in predictive analytics, you will need python, statistics, probability, and/or machine learning skills.

If you want to make the leap from data into management, you can consider obtaining an MBA or a masters degree in Management Information Systems. I happen to have an MBA from the Georgia Institute of Technology and a masters degree in Information Management from Syracuse. This may seem like a bit of overkill but I work in consulting where credentials are overly appreciated by clients (and I am a lifelong learner).

Interact with my Tableau resume here.

Conclusion

A career in data can be fun (in the early learning phases) and lucrative (mid to late career). In my case it has been a fulfilling career ever since I started work as a data analyst at General Motors many years ago. I turned myself from a commodity to a differentiator by not only learning the basics but also adding business understanding and a willingness to share what I know on this blog and my YouTube channel. I know that you can do the same. If you put in the time to learn along with the perseverance to land that first data role, you won’t need much luck at all to accomplish your goals.

Looking to land that first role or trying to move ahead in your current role? Then check out this post for the Keys for a Successful Career as a Data Analyst.

-Anthony Smoak

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!!

Build Better Sparklines in Tableau

So you want to add some spice to your bland looking Sparklines in Tableau? You have come to the right place (start by watching the video above). Let’s talk about how a Sparkline is defined per Wikipedia:

“A sparkline is a very small line chart, typically drawn without axes or coordinates. It presents the general shape of the variation (typically over time) in some measurement, such as temperature or stock market price, in a simple and highly condensed way. Sparklines are small enough to be embedded in text, or several sparklines may be grouped together as elements of a small multiple. Whereas the typical chart is designed to show as much data as possible, and is set off from the flow of text, sparklines are intended to be succinct, memorable, and located where they are discussed.”

Here are a few examples of Tableau specific sparklines in action (with latest complete month bubble indicators and reference lines): Notice how I do not include any data axes, but you can clearly recognize the data trends in the visuals.

Here is an example of how I used the sparklines demonstrated in the video to build a out a classic yet refined looking Tableau dashboard.

Interact with and download this workbook here.

For reference purposes I am going to list three formulas used in the completion of the sparklines, you’ll have to watch the video to learn how to put them together.

In this exercise I am using that standard Tableau Superstore data set which you can perform a Google search to find if you are using Tableau Public.

Calculated Fields

Calculated Field #1 (Name: SPRK_CircleMonths)

This calculated field puts a circle on the penultimate month data points. Penultimate is just a fancy SAT word way of saying “next to last”. When the month of the data point on the line chart (Order Date) equals the next to last order date month in the dataset, then return the Order Date.

//IF THE MONTH OF THE DATE ON THE LINE CHART EQUALS THE MONTH-1 OF THE MAXIMUM DATA POINT
// THEN RETURN THE DATE
If DATEPART('month',[Order Date]) = DATEPART('month',dateadd('month',-1,{MAX([Order Date])}))
Then [Order Date] END

Calculated Field #2 (Name: SPRK_CircleMonths)

This logic will be applied to the circles generated by the previous calculation SPRK_CircleMonths. Only the next to last month will meet the TRUE condition (which will be colored as red).

// IS THE MONTH OF THE CHART DATE EQUAL TO THE MOST RECENT DATE MONTH MINUS 1 MONTH
// E.G., NOV 2018 = NOV 2020 WILL RESOLVE TO TRUE DUE TO MATCHING MONTHS
DATETRUNC('month',[Order Date]) = DATEADD('month',-1,DATETRUNC('month',{max([Order Date])}))

Calculated Field #3 (Name: SPRK_RefLine Profit)

This logic will return the profit associated with the next to last month in the dataset to display on the reference line.

// RETURNS A VALUE USED FOR THE REFERENCE LINE
// IF THE MONTH OF THE DATE = THE MONTH OF THE MAXIMUM DATE MINUS 1 MONTH (GET A COMPLETE FIRST MONTH)
if DATETRUNC('month',[Order Date]) 
= DATEADD('month',-1,DATETRUNC('month',{max([Order Date])}))
THEN [Profit] END

When you put all the functions together in a manner according to the video, you end up with a more refined sparkline in my opinion. Big shoutout to the Data Duo for the inspiration on the dashboard I created and this technique. If you haven’t checked out any of their work make sure to do so.

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

Top 10 Functions in Tableau You Need to Know

Welcome Tableau learner!

Normally my videos are geared towards the intermediate level user in Tableau but in this post I want to share a video I recorded that tackles basic functions in Tableau that you should know. You will NOT be effective analyzing data in Tableau if you do not have a basic understanding of these functions.

I love to use data from basketball-reference.com in my videos. Specifically you can grab the player statistics I am using in the video here.

This video is so good, it received a mention in the Monthly Tableau roundup. See for yourself!

Also, here is a link to all of the Tableau functions from the knowledge base.

Please make sure to share this link with a new Tableau user in your circle and let me know what you think of the videos in the YouTube comment section.

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

Easily Unprotect a Sheet in Excel if you Forgot the Password

How do I unprotect an Excel worksheet if I forgot the password? I’m going to come to your rescue. This tip will come in handy when you are under pressure to make some changes and don’t feel like performing massive rework. Save yourself some time and watch the video.

You can follow along with the steps below, but give the video a try if any of the steps are unclear.

Let’s say you want to unprotect your file but you get a message like the one below when you try to change a cell value.

  1. First make a COPY of your protected file in order to avoid potential corruption. Make sure the File name extensions box is checked on the View ribbon in File Explorer.

2. Change the file name extension from .xlsx to .zip

3. You might see a text box appear that says, “If you change a file name extension, the file might become unusable. Are you sure you want to change it?” Select Yes.

4. Double click on the zip folder to open it. Make sure you have a zip file program installed on your Windows machine.

5. Navigate to the xl folder and open this folder.

6. Next navigate to the worksheets folder and open it.

6. Open the .xml sheet that corresponds to the sheet you’d like to unprotect. In my case this is sheet1.xml

7. Within the .xml file perform a CTRL+F to find the term “protection”. You should have a hit on the term if your excel sheet is protected.

8. Do not change anything in the file yet. Make a copy of this file and copy it to your desktop or another safe place to where you can easily navigate.

9. Open the copy of your .xml sheet in notepad.

10. In the copy of your notepad .xml file search once again for the term “protection”. When you find the appropriate section, make sure to delete the entire line between and including the opening and closing < > values.

11. Save your notepad file and copy the desktop .xml file back into the zipped folder. It is ok to copy and replace the file at this point.

12. Rename the .zip file back to .xlsx and your file icon should turn back into the appropriate Excel icon.

13. Congratulations!! You should be able to open your file now without the password protection popup box and make appropriate changes. Leave me a comment on the video if this tip helped you out in some way.

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.

Tableau Tile Map Data Project

I was recently inspired by some really great tile-maps that have been created in the Tableau community (e.g., see beautiful work by Chimdi Nwosu and Michael Dunphy). Thus, you know I had to come up with a way to construct a simplified map in this style with some data and share with my followers. In these two videos, I’m going to walk you through how to prepare the necessary data file in Tableau Prep Builder and then we’ll build out the tile-map in the second video, step by step.

This is a good intermediate level portfolio project for you to follow along with in order to increase your Tableau Desktop and Tableau Prep skills. We’ll use CDC data, specifically United States COVID-19 Cases and Deaths by State over Time, to build the tile-map.

The advantage of a tile-map is that it represents geographic regions (like states) at equal sizes. Thus, the distortions and biases introduced by differences in sizes are eliminated. In the case of the United States, data for smaller regions like Washington D.C. can be interpreted on equal footing with data for a much larger region like California.

Tableau Prep Builder helps to greatly simply the data shaping process. My only wish is that Tableau would integrate Prep into Tableau Desktop for one seamless data tool to rule them all, but I digress. The process below illustrates how simple it is take some data from an input file, and subsequently clean and pivot the data into a new file. Watch the first video, to learn how to build out this simple flow in Tableau Prep. If you do not have a copy of Tableau Prep, you can complete this lesson on a 14 day trial license of the tool, which you can download here.

Watch the second video for the step by step instructions to build out the tile-map above.

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

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

Tableau Ambassador Swag for 2021

I was very honored to be selected as a Tableau Ambassador for the second time in 2021. If you follow this link, you’ll see me Anthony Smoak, suited up in the Tableau Public Ambassadors section. I started this blog and YouTube channel as a means to share what I know and what I learn and will continue to do so. The fact that I gain some recognition from Tableau or anyone else is secondary, but I will say it does feel good to be acknowledged by the wider Tableau community for all the off-hours and weekend work it takes to produce stellar content.

I don’t share 10 videos in a week. I have to research what to share, put together a dashboard to illustrate the concepts and record and edit everything all in my “spare” time. Who has any of that these days when we’re all feeling worn out? However, this blog is worth it for me because it forces me to learn and I have grown tremendously since I started this social media sharing journey some years ago.

I mention all of this to simply say thanks for following me and whoever keeps nominating me as an ambassador every year, I’m not mad at you!

One of the cool aspects of being selected as a Tableau ambassador is the swag you receive. Definitely watch the video as I will share an unboxing of this year’s swag for those that are curious.

In the words of the legendary Q-Tip from A Tribe Called Quest , “So give me this award, and let’s not make it the last.”

-Anthony B. Smoak

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

If you want some of the cool merch I’m wearing in the video, check it out here.

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.