Tableau Christmas Card Mail Merge

Christmas Cards

I love getting Christmas cards! But sending them can be a chore1. Fortunately, there is Tableau, a sophisticated-yet-intuitive-to-use rapid data discovery/analysis/visualization/story-telling platform which I will now use for mail merge2.  That’s right, just get a list of contacts, a stack of envelopes and a printer, and fire up Tableau.  Your days of laboriously hand-writing addresses are just about over3.

Step 1: Collect the data

This step is easy.4 The ultimate goal is a simple list of names and addresses like this5

Data

Step 2: Create the view in Tableau

This step is also easy.

View

I’ve placed every address field on Text.  Spacer is a calculated field which is hard coded to a bunch of spaces.  Return Address is a calculated field that allows me to use a return address entered via parameter.  There are several other key things to notice about the view:

  • I removed row and column dividers (because who wants to see that printed on the envelope?). To do this, I simply selected Format à Borders from the menu and then selected None for Row and Column dividers.
  • Name has been placed on the Pages shelf. Pages is one of the most under-used aspects of Tableau.  It can be used to create amazing motion charts.  Here, I’m using it to define a printed page.  A “page” defined by the Pages shelf is not always equivalent to a single printed page.  For example, a long text table may have a single “page” that spans multiple printed pages.  But the “page” defined by the Pages shelf gives a page break.  Here, my view is not large enough to worry that it will take more than 1 printed page to print a “page”.
  • I edited the text (click on the Text shelf). You’ll notice the Spacer calculated filed which gives consistent spaces (I could have just entered them manually) and the Return Address calculated field (which I could have also entered manually):

03 - Text

Step 3: Setup the Page and Print

From the menu select File à Page Setup.  The page setup is specific to a single view and allows you to set various options such as what elements of a view are included in the print, the layout, and print scaling.  The key here is to include only the view and make sure the option for printing all pages from the Pages Shelf is set.  On the Print Scaling tab, set the scaling to no more than 1 page down and 1 across and the page orientation to landscape.

04 - Page Layout

You may have to fight a bit with your printer – but at least the Tableau part was easy!

05 - Envelopes

 






—————————–
References:

  1. Actually, my wonderful wife does most of the work. I cannot even begin to express my gratitude!
  2. I recently asked on Twitter if there was anything that shouldn’t be done with Tableau. I got back some great responses. Look for a blog post soon with my answer – but in the meantime, no one specifically said, “Don’t use it for mail merge.”
  3. I know. It was really over in 2004 when everyone started sending e-cards.
  4. Do it however you like – use Alteryx, export to CSV from your CRM, do some web-scraping, type it in by hand, etc…
  5. The names are real, but the addresses are clearly fake. I use the real ones every year but haven’t gotten any responses. If you have any updated contact information, please let me know.  But I’m really hopeful that since I’m using Tableau this year that Data will respond.  Data always responds to Tableau.

Dynamic Annotations (Part 3)

Often, there is no “right” way to accomplish something in Tableau. There are often many ways to approach a solution.  Sometimes there are “better” ways.

I’ve been thinking for a while about a couple of my earliest posts (Dynamic Annotations part 1 and part 2).  It occurred to me that I had very much over-complicated the solution.  The more I understand how Tableau works, the simpler some things become.  It can be fun to struggle and come up with unique and creative solutions but it can be equally satisfying to understand why things work and have clarity on how to achieve certain results.

Dynamic Annotations is one such case.  Here is the easy way:

 

I had previously realized that a mark annotation was associated with a mark and would only show if the mark was shown.  My solution to turn annotations on or off was to show or hide certain marks using various calculated measures and dual axes.  Whoa!  Way too complicated…

What defines a mark?  When you see a bar or a circle or shape in Tableau or when you see the text “17 marks” in the lower left status bar, what is it that define those marks?

Tableau draws a mark for every intersection (tuple) of dimension values.

17 Marks based on combinations present in the data.
17 Marks based on combinations present in the data.

 

Various other factors can come into play.

Data densification can complete domains of dimensions and cause more marks to be drawn:

51 Marks based on  intersection of completed domains of dimension values
51 Marks based on intersection of completed domains of dimension values

 

Multiple axes can duplicate marks (once for each axis):

34 Marks based on intersection of existing values, (17 marks per axis)

Mark annotations are applied on a mark.  But what defines whether a specific mark annotation is shown is not just the intersection of dimension values.  There are two major factors:

  • The placement and order of any fields on the Rows or Columns shelf (the fields that define the X and Y position of the mark)
  • Any field used as a dimension on the Marks card or Pages shelf (fields that add additional values to the dimensional intersection / tuple).

The distinct combination of values for those fields defines a unique mark annotation which will only be shown for that specific set of values.

All of that may seem rather complex — but it leads to a very easy way to create dynamic annotations in Tableau.  You can have sets of annotations controlled by parameters or include fields as dimensions on the marks card that will change values based on action filters in a dashboard.  The possibilities are endless for dynamically controlling which annotations are shown.

Here’s how I created the sets of annotations shown above:  First, I created a parameter that allows the user to decide which set of annotations to see.  You’ll notice I used a list of integers with “Display As” text.

Parameter with 4 options
Parameter with 4 options

Then, I created a calculated field that simply returns the selected value of the parameter.

Calculated field that returns selected value of parameter
Calculated field that returns selected value of parameter

Then, I just placed that calculated field on the Marks card as a dimension (remember, that is one of the key factors).  Then, as I cycle through the parameter options I can annotate marks with whatever annotations I want to have displayed for that particular selection of the parameter.

Final

 

Wow!  That was far easier.

Honored and Humbled – Becoming a Tableau Zen Master

First, I need to say I have the best co-workers.  They are colleagues and friends.  This was on my desk when I returned to work after conference:

Zen-Garden

 

If you’re looking for a place to grow, learn, teach, and serve others — you can’t do better than Teknion Data Solutions.  Thank you to everyone there for all the support and opportunities!

When you listen to the response of newly inducted Zen Masters two words stand out: “honored” and “humbled.”

“Honored” makes sense when you first hear it.  It is indeed a high honor to be named Zen Master by Tableau.

But what about “humbled”?  Why is that the other term that Zen Masters are likely to use to describe themselves?  I’ll speak for myself.  Here are some reasons I feel humbled:

  • I’ve been named among many who I consider to be my mentors.
  • I work with and meet many people who teach me new things every day.
  • I stand on the shoulders of giants.  What I know is made up of what I’ve learned from others.
  • There’s a lot I still don’t know.  Zen Master doesn’t mean “mastery” in the sense that I know everything.  Far from it.  I’m still learning how much I don’t know.
  • There’s a lot I will never know.  And that’s okay.  I once naively thought that one really could know all there was to know about Tableau.  But even if you could master every last menu option consider all the areas Tableau touches: databases, data structure, analysis, statistics, R, GIS, writing code, visualization, design, server administration, and more!  Who can master it all?  No one.  Should we despair? No!  First, Tableau is so incredibly intuitive that anyone can start using it to create beautiful and useful things without a steep learning curve.  Second, I am incredibly grateful for a vibrant and dynamic community that freely shares their specific knowledge and helps each other.

Humility is a lesson I’ve learned and I’m still learning it.  A  couple of years ago, while attending my first Tableau conference, I was introduced to one of the original Zen Masters.  I was introduced as a “jedi” and I thought I was.  I had been using Tableau just long enough that I knew a fair amount but didn’t yet realize how much I didn’t know.  Sensing my know-it-all attitude, this individual patiently showed me a few things I had never considered and then kindly invited me to get in touch if I wanted to understand it more.

It ignited a passion in me to learn more, deepen my understanding, and help others with the knowledge I gained.  Along the way, I’ve continually been humbled as others have patiently and kindly corrected my “correct answers” on the online forums,  challenged my assumptions, or confirmed my understanding.  I am incredibly grateful.

Honored? Yes.  Humbled? Definitely.

 

Blackjack! (Tableau deals)

Can’t wait for the Tableau conference next week year? How about playing a game of blackjack against Tableau in the meantime? No, you won’t win real money. But you can sharpen your skills in case a future since the next conference is in Vegas.

By the way, this dashboard uses a little known fact that secondary data sources are many times refreshed only from the cache even when the primary sends a new query to the data source. Want to discuss? Ask me questions, provide feedback and critique in the Tableau Community Viz Talk

Just remember to press F5 (refresh the browser) after you play — or the cards won’t be shuffled (of course, you could use that to your advantage by just pressing the revert button – though sometimes the dealer still likes to shuffle).

The Story of the Second Punic War

I love history, but I find it difficult to remember all the details. That’s mostly the way it’s taught and presented. Lectures, lots of reading, lists of names and dates, facts and figures.
But all of that can be told visually as a data story! And that makes it easier to remember and understand.

So, here’s my contribution for Storytelling month at Tableau Public. I’ve always been fascinated by how close Hannibal came to conquering the Roman Republic. I’ve thought repeatedly about crating a dashboard to explore the battle of Cannae (ever since reading Cannae by Adrian Goldsworthy.) With Tableau Stories, telling the story just got a lot easier.

By the way, did you know that Charles Minard plotted Hannibal’s invasion of Italy?

Minard

 

Of course you did.  But I didn’t, until after I had created the dashboard.  Of course, I was inspired by his more famous visualization of Napoleon’s March.  And his representations are much more precise than mine.  I went for accuracy, but was a little loose with precision (especially latitudes and longitudes — in other words don’t use my numbers for your doctoral dissertation).

And here it is:

(I’ll come back to Tic Tac Toe, I promise!)

Creating Tic Tac Tableau Part 2: 5 Million Records – Really?

If you missed them, see the original Tic Tac Toe Dashboard and Part 1 first.

Chris Love asked if it was really necessary to have 5 million records. After all, there are only 19,683 possible variations of boards – even less when you consider what would be a valid board.

(By the way, check out Chris’ post Generating Tic Tac Toe Data the Alteryx Way which has caused me to bump “Learn Alteryx” up on my to-do list. Visually working with the data beats coding any day. That’s why I love Tableau! And I’m really hoping to see an Alteryx solution that incorporates the complexity below.)

At first, Chris’ question caused some self-doubt. Maybe I had over-complicated things and really should have had 19,683 records of data instead of 5 million. That would have derailed the posts I had planned to explain how I turned 5 million records into less than a million!

But then I remembered why I needed such a large data set. What makes the Tic Tac Toe dashboard work is not having every possible board. It’s having every possible move that leads to every possible board. You can see that here:

boards - parent, children, grand-children

You’ll notice Board 1 at the top. That’s the starting point. X can play in any empty square, which represents 9 possible children boards for Board 1 (only 3 are shown). For any one of those boards, O has 8 possible moves. Those represent the grand-children of board 1. So there are 9 children and 9 X 8 = 72 grand-children of Board 1. So, just 2 plays into the game, there are 72 possible outcomes, but 72 + 9 combinations of moves. When you work your way through to every possible outcome, there are (9 X 8 X 7 X 6 X 5 X 4 X 3 X 2 X 1) + (9 X 8 X 7 X 6 X 5 X 4 X 3 X 2) + (9 X 8 X 7 X 6 X 5 X 4 X 3) + (9 X 8 X 7 X 6 X 5 X 4) + (9 X 8 X 7 X 6 X 5) + (9 X 8 X 7 X 6) + (9 X 8 X 7) + (9 X 8 X 7) + (9 X 8) + 9 possible moves. Except that some moves result in wins before every space is used. Then, multiply the result by 9 (one record for every space) and the end result is nearly 5,000,000.

That’s the number of records required for the full data set required by the Tic Tac Toe dashboard. Additionally, to allow Tableau to understand how to play, each record must have these elements:

  • Board ID: the ID of the current board
  • Child ID: the ID of the board resulting from the move. This will allow the action filter to work
  • Parent ID: the ID of the board that preceded this one. The child ID alone would be enough except that the dashboard resulting from a click will always skip a generation. In the example above, Board 1 will be shown but no board from the second level will ever display. Upon clicking, Tableau will select a board from the next generation. The third level represents the resulting board after Tableau has taken its turn. Thus, the grand-children are the set of boards from which Tableau must pick the “best”
  • Game Status: In Progress, X Win, O Win, or Draw. Tableau should select O Win, if possible
  • Leads to Inevitable Loss: the board will result in a loss for Tableau (so, Tableau needs to not select it). This was a late addition to the data set (so it’s not in the code). I added it after the fact, once I realized it was necessary.
  • # X Wins: the number of X wins possible from this board (Tableau needs to minimize this)
  • # O Wins: the number of O wins possible from this board (Tableau needs to maximize this)

Now, out of the 5 million records that result from every combination of every possible move, I can eliminate some records and use some tricks to have Tableau supply missing data I remove from the source. I’ll show how in future posts…

In the meantime, here’s a view of the first 30 possible boards (with 9 spaces for each board)

boards - better view

Creating Tic Tac Tableau Part 1: Generating the Data

Once I had the idea of creating a dashboard to play Tic Tac Toe against Tableau, I had to come up with a way. I knew I wanted to use actions in a dashboard – and that meant I’d need dimensions to pass as filters. That meant I’d need to have data at the level of a space on the board. There are nine spaces. But I’d also need to have every possible move so that clicking on a square could send an action filter specific to a given board. So I’d need every possible board. This is more than just the possible outcomes of the game – in fact, even more important, I’d need every possible board of every possible game at every possible state: Win, Draw or In Progress.

I’ve never seen such a data set. So, I set out to create one. I wrote an application in C# to generate the data as flat files. I subsequently imported the data into SQL Server so I could manipulate it as needed. I won’t go into the code (you can download it here), but here’s an illustration of the recursive process it used to play through every possible game:

First 18 boards

Notice that every board has 9 records of data, one for each space – even empty spaces. In fact, especially empty spaces as those are the ones that will eventually trigger the action filters they must be in the data.

Starting with an empty board, the application placed an X in the first available spot (board 2), then an O in the next (board 3), then an X in the next (board 4), and so on. At every point, the application checked to see if the game was a final outcome (X Win, O Win, or Draw) and if so would stop, go back to the previous board and try the next possible play. Notice that board 8 is an X win. Board 9 then is a continuation of board 7 with the next possible X move.

When I first wrote the application, I had no idea how many iterations it would take (I’m sure I could have calculated it), how long it would run, whether it would consume all my memory and blow up, or how many records it would create. It actually worked very well, going through every possibility and writing the data out in a matter of seconds. I was excited…

…but then I opened the file and did a record count. My heart sank…

Although I only had 549,946 possible boards – each of those boards had 9 spaces or squares. That meant I had nearly 5 million records of data. That’s not too much for Tableau at all. I work with many times that amount of data every day. In fact, you can download the full data here as an extract in a packaged workbook.

But it is over the 1,000,000 record limit for Tableau Public! And I wanted to share Tic Tac Toe with the world.

So…

…I got creative. I’ll show you how in Part 2.

Prevent Global Thermonuclear War: Play Tic Tac Toe with Tableau!

Ever since Noah Salvaterra raised the possibility that Tableau could become self aware, I’ve been unable to sleep. The thought of sentient machines is unsettling. But then it occurred to me: the first thing you must do with any sentient machine or program is to teach it to play tic tac toe (to avoid global thermonuclear war, of course).

So…

…I taught Tableau to play Tic Tac Toe! And it (he? she?) is indeed really playing! I had to give her the data and a set of rules. And now she wins or at least comes up with a draw nearly every single time. (There is actually one set of moves that gets you a win – if you can suggest a rule that would avoid that, please let me know edit: it wasn’t a rule issue, it was a data issue — now fixed ).

This was no small task. It makes the Choose Your Own Adventure dashboard look like child’s play. And indeed, I promised to give some explanation on the workings of that dashboard and am delinquent (because I got caught up in this project!). I beg forgiveness and make another promise: I’ll return at some point to give explanation for that one after giving some explanation for this one.

The explanation won’t be brief. But it will be fun and, hopefully, enlightening.

There are only 255,168 possible outcomes of the game (not including board symmetry). But I needed data at the level of detail of every possible move that leads to those outcomes — which means I had 4,949,514 rows of data. That’s not big data by any means – but it does exceed the 1,000,000 Tableau Public limit! Oops!

So, in a series of future posts I’ll explain the gymnastics required to build this dashboard. It will include things like generating the data set, culling the data, data densification (both at the source and in Tableau), some crazy data blending, slicing up data sets, action filters, sheet swapping, heuristics, and more! Hang on… …it’s going to be a fun ride!

In the meantime: play some tic tac toe. And save the world!

Choose Your Own Adventure! Pushing the limits with Tableau

I used to read Choose Your Own Adventure books when I was a child and I always wanted to write one. Specifically, I wanted to write an electronic version.  When I was 10 or 11 (back in the early ’90s!), I remember trying to create one using Basic. My dad told me that I shouldn’t try to hard-code all the text as strings and instead store it in a file. Turns out he was right!

 

Fast forward a few years and I get the thought that maybe I could create a Choose Your Own Adventure dashboard in Tableau.  It seemed like a possibility, but there were definitely some hurdles. For one thing, how could you have a choice that would reveal an entirely new set of choices when you selected it – without allowing the user/reader the option of changing their mind and selecting something else?  After all, sheets that trigger action filters, parameters, and quick filters are all still there once you make a selection. Don’t like the outcome of the story? Just make another selection.  (Granted, I’d keep my finger in the pages of the physical Choose Your Own Adventure books to go back and find alternate endings.)  But that’s not the way I wanted the dashboard to work.

 

I love pushing the limits with Tableau! A few months ago I started playing around with all the possibilities available using Sheet Swapping.  If Sheet Swapping on Steroids was what got a ball player kicked out of the league, then this dashboard is what turned Steve Rogers into Captain America. But even more, this dashboard is a shout-out to many of the wonderful individuals in the Tableau community – which is the best out there!

 

Stay tuned for a post or two that goes into “how it works” but for now, have fun:

 

Edit: I think I’ve got the error that resulted in an intermittent blank page resolved. (Hope so, at least). There were a few other things I fixed too… who knows what I broke along the way…

 

3 Easy Tableau Tips for Confident, Clean-Shaven Maps

Wow! Tableau Tips Month is almost over and I almost missed it! (Or maybe I was just early with this tip: 1 Easy Tip to get Clustered Bar Charts )

So to make up for my tardiness, I’ll give 3 easy Tableau tips for the price of 1!

Imagine the following maps as components of a dashboard:

Map #1

Map #1: Withdrawn, shy, hasn't shaved in a couple of days
Map #1: Withdrawn, shy, hasn’t shaved in a couple of days

Map #2

Map #2: Bold, Confident, Clean-Shaven
Map #2: Bold, Confident, Clean-Shaven

 

Which do you like better? Both are using the exact same data. Map #1 is a little shy, withdrawn, and looks like he hasn’t shaved in a couple of days. Map #2 is clean, confident, and ready for business. And here’s what you really wanted to know: it’s easy to get from Map #1 to Map #2. Just remember these 3 easy tips:

 

Tip #1: Give your map a shave

If you are using a filled map, then, unless it really is important that your audience knows the context (e.g. does your audience not know the 48 contiguous states are south of Canada and north of Mexico?), you can get rid of the background map and just keep the filled shapes.

How? It’s easy. From the menu select “Map” -> “Map Options”. Then turn the washout to 100%. Now you have a nice clean-shaven map.

Map Options

Tip #2: Give your map confidence

This tip comes from my co-worker, Nathan Mackenroth. This tip alone is worth the price of admission. When I saw it for the first time I was amazed.
Notice that in the first map Texas and California have some variation of color but everything else just looks like the color of silly putty? The reason is that Texas and California have the most visitors by far and so any variations in other states are overshadowed. Prepare to be blown away at how simple it is to get from shy to bold:

 

Nathan took the measure Visits that was on color and created a calculated field which he then put on color instead:

LN(SUM(Visits))

Brilliant! Just like a logarithmic scale for an axis – but for color! Now I can still see that Texas and California have the most visits but I can also see which states have the least and I can see variations one state from another. And it isn’t just for geographic maps – try it with tree maps and heat maps too!

Tip #3: Give your map a chaperon

Don’t send your map into the world alone. For all the confidence you just gave him, he’s going to be just like that teenager who thinks he knows everything, but is about to make a fool of himself. Here’s the problem:

Problem Child
Problem Child

 

Really? I had 2.7 visits at the least to 9.7 visits at the most? No, that’s the natural log. So the truth is, we really do need shy Map #1!

And here’s the key: we don’t need to see Map #1, but we do need to see his color legend. So, on a dashboard containing Map #2, add Map #1 as a floating object. Use the pixel perfect controls in the lower left to make him 1 pixel by 1 pixel and put him out of sight. (He’s okay with that: he’s an introvert)

 

 

Pixel Perfect Controls

 

And now, we have a map that is ready to take on the world:

Map in his very own dashboard
Map in his very own dashboard