• bkornell

Text Tables! Tackling Week 3 of Back to Viz Basics

Eric threw a curveball this week by asking #B2VB (Back to Viz Basics) participants to "Build Your Best Text Table". Seriously?!? We got into data viz to make pretty charts, not to build boring Excel-style tables!

News Flash: Every real-world employer is going to ask for text tables. Smart vizzers will layer visual elements and Tableau-specific functionality over the text table, and they will gently explain to the Powers that Be when it would better to switch to Excel.

I used the opportunity to practice text formulas and to implement sort order buttons, a new technique for me (see below).

Who is This Viz For, Anyway?

Before designing a viz, I ask myself two key questions:

  1. Who is the user?

  2. What questions do I want them to answer with the viz?

The data set this week is massive: over 270,000 rows of individual athlete and event data. Based on the text table prompt, though, I wanted to get really granular. So my answers were:

  1. The user is a fan of their national Olympic team.

  2. They should be able to look up previous Olympics to see who their country's star athletes were in their favorite sports.

I was pleased to see that at least one user did exactly this! @joberger83 commented on Twitter that he looked up the German basketball team that faced America's first "Dream Team" in 1992. (Unfortunately for Germany, it was a 111-68 rout in the preliminary round.)

Remember, a single viz can't answer every question! My viz is restricted to a single country at a time; it doesn't show the "medal race" between countries, for instance. Plenty of other great vizzes are focused on that question.

Special Characters in Formulas

The Medals column in the viz isn't a circle mark type; it's a Dimension on Rows! Instead of using the word "Gold", I added a black circle and then formatted the column to be yellow.

To implement this trick, search for "Large Black Circle ASCII" and copy/paste this character into your formula: ⬤.

This is handy for rows, because the dot can be on the left side of the columns rather than all the way to the right with the other marks. I use this often in business settings to make red indicator dots in tables: . I also worked on a COVID study about a year ago where we coded fully vaccinated as ⬤ and partially vaccinated as ◯. It works best when dots are on the minority of rows, because they will yank the reader's attention. That's the case with medalists in the Olympics.

Tableau can use emojis, too! You could also search for "First Place Emoji" and paste 🥇 into the formula. I chose a circle because I felt it was cleaner, and Olympic medals are round anyway.

SPLIT() with Negative Numbers

Team rosters are typically sorted by alphabetically by last name, but the [Name] field wasn't separated. Tableau's SPLIT() function will break apart a string using a separator character like " ", "-", or "/". But the names all had different numbers of words in them, so I couldn't just grab the second name with

SPILT([Name], ' ', 2).

New discovery for me: you can use negative numbers to get the last word in a string! So this is the basic way to get the last name:

SPLIT([Name], ' ', -1)

To calculate the first name, you could use REPLACE() or FIND()/LEFT() to figure out the position of the last name in the string and remove it. I was a little worried about edge cases where the last name was repeated elsewhere, though (e.g., "Christina Chris", "Hong Ho").

I realized that I could also use the same negative number trick to avoid this: combine the 10th word from the right, the 9th from the right, and so on. TRIM() gets rid of the unneeded spaces.

I did a little more work to handle things like "Jr.", "II", and pre-marriage names in parentheses, but in the end I wound up with calcs that work for almost all Western-style names (with the family name coming after the given name).

Hidden Dimensions on Rows

I wanted to sort by last name, but I didn't want to order the columns "Last", "First". Olympic athletes' names are very familiar to users, like brand names. It's jarring to see "Lewis [big space between the columns] Carl"; I want to see "Carl Lewis". Besides, the original "Name" field is already formatted so pretty.

I added [Last Name] and [First Name] to Rows, and then unchecked "Show Header" on each. This has the effect of sorting by those fields alphabetically (by default), but not showing them.

In Tableau, there are sometimes multiple ways to do things: a different option would be to build a [First, Last Name] field, then set the sort order on [Name] to use that field.

Sort Order Buttons

Finally, I wanted to try a technique I'd seen on Playfair Data: changing the sort order with triangular buttons. To my surprise, the technique only took a couple of minutes to set up, needed no new data sources, and used default shapes in Tableau!

Refer to Ryan Sleeper's blog How to Change Sort Order with Buttons in Tableau for step-by-step instructions.

The core of the technique is a parameter action, everyone's favorite Tableau feature. My spin on it is a little more #WorkoutWednesday: there are two parameter actions: one for the sort field (sport, gender, etc.) and another for the sort order (ascending, descending).

Remember the bit above about hidden dimensions? My final viz actually used three ascending sort fields and three descending, all hidden. To keep the sort "natural", there are multiple levels: for instance, if you sort by Gender, it then sorts by Sport and Event Name (so "Men's Archery" still appears first).

And that's the text table challenge! As I mentioned above, you'll never get away from building these in business scenarios; they're very useful for users who want fine-grained detail. So give them a Tableau makeover, and let's see what you come up with!

49 views0 comments