Thankful Thursday – Mark Jackson

As I neared the end of my first year using Tableau, I was starting to feel more comfortable with building vizzes and dashboards, but they were all pretty static. Everything was white background and tiled (I hadn’t had the container enlightenment yet). Then I came across Mark Jackson‘s Quantified Self / Career Bio on Tableau Public.

This was kind of a revelation to me. I had been following Viz of the Day on Tableau Public and got to see some really cool examples, but the combination of viz elements with the graphics and how they all intertwined was kind of mind-blowing to me. But I thought it would be a great learning experience to figure out how Mark built it, so I decided to make my own version.

This project helped me really cement a few things in my use of Tableau. One of the first was identifying how data should be best organized to build a viz. All of this data was self-quantified, so I had to think through how it should be structured and went through a few iterations as I developed. This was also the first time (I think) I had ever built a whole dashboard with floating elements. I think it was also my first experience really using shapes, particularly custom shapes and identifying that I can just drop images into the Shapes folder of My Tableau Repository and they are available to use in a viz.

As I think about these things now, they all seem very basic in comparison to many of the things I’ve learned in the 4 years since that time. But early in my Tableau journey, building my own iteration of Mark’s viz helped me really broaden my understanding of what could be done in Tableau and laid a foundation for future growth.

So thanks, Mark, for the inspiration early on in my journey.

Here’s my viz (admittedly a bit out of date):

Workout Wednesday – Week 7

This week’s challenge by Lorna was a crash course in Parameter Actions. And by crash course I mean this was the second week in a row I was bumbling around with my screen shared for others while I tried to figure out what the heck I was doing. Rather than peek at Lorna’s workbook this week (like I did last week on Ann’s challenge), I started scrolling through Lindsey Poulter’s Twitter feed knowing that I would surely find something that would be applicable. I found it in her Makeover Monday Matching Game. So everything in this post about how to accomplish the parameter actions I learned from Lindsey’s viz.

I started out just building the bars with the target lines to get warmed up. At first I just kind of plugged some numbers for the target to get set up until I had the parameters all figured out.

So in order to get the bars with the Gantt bars, I had SUM(Sales) and the Target value on dual axis, with Category on the rows.

With that base set, I built the Forecast and Target Labels. Once again, Category on rows for each, and then I just pulled category onto detail and changed the mark type to Circle for Forecast and Shape for Target (since they were empty/filled circles).

(Ok, I totally breezed through that…mostly because I’m afraid of how long it’s going to take me to go through the parameter actions, but also I feel like most of that is fairly self-explanatory, and I’ll get back to it more with the actions stuff)

Parameters!  First I created the Sales Forecast parameter:

Then the Sales Target parameter:

I decided, as I looked over Lorna’s viz, that I would need to build parameter values adding each category and value as the category was clicked on. Due to the nature of parameters, I knew I would need two, one for the category, and one for the value of the forecast/target. As I thought threw it, I figured they all needed to be strings, so I created four string parameters with an empty value (Forecast, Forecast Value, Target, Target Value):

On to Lindsey’s wizardry…(I’ll discuss Forecast, and Target is pretty much mirror image) In order to add values to a parameter as you click on things, you need a calculation to check if that value is already there, and if not, add it. Here’s what that calc looks like:

That one builds my Forecast parameter with the categories that have been selected. But per Lorna’s requirements, we also need to capture the amount typed in the forecast box when each category is selected. That requires a similar calc:

Notice that I’m starting with the [Forecast Value] parameter, checking if the [Forecast] parameter contains [Category], then adding the [Sales Forecast] parameter value (in string form) to the [Forecast Value] parameter.

Now we need the Parameter Actions to pump this data in. In order to make it work, we need the above calculations in the detail of the Forecast Labels sheet:

Then on the dashboard, we add the action:

This says on Select in Forecast Labels, pass the [Forecast Param Value] into the [Forecast] parameter. The Forecast Value action is similar:

At this point I chose to show the parameter control for Forecast and Forecast Value so I could see what was happening. And it worked!

Now that we’re capturing these values, we have to turn them into the thin bars on the bar chart. But how do we tie the category in [Forecast] to the value in [Forecast Value]? I started by identifying the 1st, 2nd, and 3rd values of each parameter, like this:

(Forecast Value 1,2,& 3 were similar, just with INT in front of the SPLIT in order to turn them into numbers)

With each of the values split out, I can now create the forecast value based on Category:

Basically, I’m checking to see if the [Category] is in [Forecast Category 1], and if it is, add the [Forecast Value 1] to SUM[Sales]. (I’m adding to SUM[Sales] because that’s what the forecast is doing per Lorna’s requirements) Same thing for the 2nd and 3rd calcs. If the [Category] isn’t in any of the 3, then NULL so the bar doesn’t show up.  Drag the [Forecast] onto the SUM(Sales) axis in the bar chart, and move [Measure Names] to Color and Size, making sure to turn Stack Marks “Off” in the Analysis menu. Another round of testing showed appearing bars!

Along the way I needed to build the clearing mechanism. So I created a Clear Forecast and Clear Target worksheet, with a [Clear] calc consisting of “” (yep, just two quotes). With [Clear] in the detail, I chose the Shape mark, then added an Undo icon for the Shape.

(SIDENOTE: Need icons? I usually do a Google search, but then you have to figure out if you need to attribute or what, and is it transparent, etc. I realized I could just open up PowerPoint, pick the icon, make one black and one white (since the Target version needed to be white), and save it as a PNG. Then you save them into the Shapes folder of My Tableau Repository, and you’re all set!)

After adding that sheet to the dashboard, I created another Parameter Action:

So this will just pass “” into the [Forecast] parameter, making it start fresh.

One of the extra items in the challenge was to make selections automatically deselect. I’ve talked about that here before, using Yuri Fal‘s TRUE/FALSE method. I applied this to Forecast Labels, Target Labels, Clear Forecast, and Clear Target.

For the bar labels, I created a [vs Target] calc:

Then to get the up/down triangles to show, I remembered a formatting trick I think I first learned from Curtis Harris. If you set your regular number format, you can then select Custom and tell Tableau what you want to show for positive, negative, or zero values.

In this case, my positive number is down, because those are my values less than 100% since I did 1-(sales/target). If you want another format for a zero value, you would just add another semi-colon and then the value you want.

This worked fine for my sales vs target, since it was always appearing. But when I did the same for my forecast vs target, I realized I could just hardcode the parentheses in the label, or it would just look like this () if there wasn’t a forecast. So I decided to throw the parentheses in this custom format, and it worked!

At this point, I did all the same Parameter Action work on the Target side, and created a calc for the Forecast Color (with a similar one for Target Color):

After adding those to the Forecast Labels and Target Labels sheets, I checked the tooltips, got everything sized, padded, and colored correctly in the dashboard, and tested like crazy (seriously, I think I clicked around those circles at least a hundred times). With that, we did it!

Here’s the link to Tableau Public

Once again, a shoutout to Lindsey Poulter whose work I reverse engineered to get to this point. Great challenge Lorna!

Thankful Thursday – Jeffrey Shaffer

In previous #ThankfulThursday posts, I’ve mentioned how Andy Kriebel‘s blog was first on my favorites, and Cole Knaflic‘s was second. Well, a very close third on that favorites list is Data + Science, Jeffrey Shaffer‘s blog.

Within the first few months of my Tableau experience, when I was googling how to do everything, I came across Jeff’s Tableau Reference Guide. As of today, this reference guide contains 737 curated links (737!!!!) to content created by Jeff and many, many others ranging from basic chart how-to to Level of Detail and heavy duty calculations (and much more).

Jeff also worked with Steve Wexler and Andy Cotgreave to author The Big Book of Dashboards. As I’ve noted previously, I love having this book as a reference to identify what works in dashboards and why, and be able to implement those principles in a work setting.

Some of my favorite Tableau Conference sessions over the past few years have been Jeff and Andy Kriebel‘s speed tipping sessions. (TC16, TC17, TC18) Jeff continued the tips tradition (with a twist) at TC19 with Luke Stanke, as they did Two-Minute Tableau Tips and Tricks with longer form tips. All of these sessions have been so valuable in identifying faster/better/more efficient ways to do things in Tableau.

So thanks, Jeff, for all you’ve done (and continue to do) for the Tableau Community.

Workout Wednesday – Week 6

I don’t know what it was about Ann’s challenge today that made it so difficult, but I S-T-R-U-G-G-L-E-D. I changed course multiple times, spent 20 minutes just trying to figure out a course of action in the first place, and thankfully got some ideas/assistance from the people in the room (all of this happens while I’m sharing my screen with several colleagues).

My first struggle was just figuring out the general layout. Ann’s hint to not use Measure Names/Measure Values just obliterated every idea I had initially of how to get these rectangles. And that was likely my problem, I thought of them as rectangles. (More on this later)

Since I was struggling with the layout so much I finally just decided that I would need the performance comparison calc, so I just as well create it. My initial thought was to do SUM([Sales])/LOOKUP(SUM([Sales]),-1), and that totally worked fine throughout the entire building process, until I got to the tooltips late in the game and realized I was going to make life difficult for myself. That’s when Yu Dong piped up and said she just created a current month and prior month calc based on a parameter. So much cleaner, no need for table calcs, creating indexes to hid the prior month, etc. So the rest of this walkthrough will incorporate that process.

So, to identify our current/prior month (and allow for someone to change the dates, per Ann’s requirements), we start with a basic date parameter:

Current value here is 11/1/2019 because Ann’s viz was based on November.

(Sidenote: You can tell how ready to be done I was at this point, after having built everything but the tooltips and needing to “start over”, I didn’t even name the parameter. Always name your parameters and calcs, kids!)

Now we need to identify the current and prior month. Current month just compared the parameter value with the first day of the month of Order Date:

Prior month does the same thing, just comparing the month prior:

Then, in order to filter the view to these two months, just need a quick combination calc to drop on the filter:

(Yep, another unnamed calc, it was bad)

Now that we have the current/prior month, we need the measures. This is another one Yu helped me clean up. I usually lean to something like IF min(Current Month) then SUM(Sales) END kind of thing, but it wasn’t really working with my boolean Current Month calc. So this is where we landed, with the aggregation outside of the IF statement:

Prior month calcs were the same, just with Prior Month in place of Current Month. Then for the performance comparison, just Current over Prior:

With all of the metrics complete, had to figure out how to get the aforementioned rectangles. Somewhere in the 20 minutes of stupor, I finally just had to download Ann’s workbook to get an idea of how she went about it. One look, and I realized they weren’t rectangles, they were bars. Once I had that insight, I jumped back into my workbook, threw some min(1)’s onto the columns shelf, and had myself some bars.

To get the color, I created a calc for each metric to check if the performance comparison was greater than 100% and set the color accordingly:

With each of the colors in place, I needed to figure out the KPI label. Since I was already identifying if current month was greater than prior in the color calcs, I plugged those in:

That gave me the proper percentages, but I needed to get the red indicator. At first I tried to incorporate it with the Overall value calc, but decided against that. I ended up copying a bullet point and throwing it in a calc:

Then I changed the mark type to text, and added Indicator and Overall to the label:

When I got to the tooltips, I realized I needed to get the month name for the current and prior month for the labels:

Finish up the tooltips and formatting, and we’re all set!

Click here to view the workbook in Tableau Public.


Makeover Monday – Week 5

This week’s #MakeoverMonday challenge put a twist on this viz about Brits’ preference for the character James Bond:

The twist was that the data was broken down by whether they voted to leave or remain in the EU during Brexit voting, rather than the population dichotomy above.

What works:

  • I actually think the stacked bars work fairly well in this case, since Acceptable and Unacceptable both start with a common baseline on each end of the bar. You can see the difference between the two groups fairly easily.
  • Sections are clearly labeled so you know what’s being measured

What could be better:

  • The population labels (which are fairly wordy) get kind of repetitive
  • By the time I get to the bottom, I’ve forgotten which side is Acceptable/Unacceptable

For my viz, I played around for about 30 minutes with different column/row combinations with the category, response, measures, etc. For a while I was starting to think the stacked bars were the best option (and a case could probably be made that they work just fine in this case). Eventually, I decided to just focus on the Acceptable responses, since the don’t knows were similar for both sides and fairly small, so Unacceptable was just a mirror image. Doing so allowed me to reduce the noise and really hone in on a comparison between REMAIN and LEAVE groups for each of the four questions. Here’s what I finished with:

Here’s the link on Tableau Public