Day 11: Switch between fields

This is a another quick one, and as far as skill this one is on the simpler end.  But if you don't already have this in the toolbox, it's a great trick to learn.  Today we'll look at how to use a parameter and a case statement to give the end user the ability to switch through different metrics. 

Learn About Tableau

Before we get into it, you should know that this trick will work with any type of visualization - Maps, Bar Charts, Line Charts, Packed Bubbles, Tree Maps, etc....  As an aside, we'll chat more on switching to different visualizations later in the month, as well.

As for today, let's get going.  Here we go with the Superstore again. We're going to do two different uses of this, starting with the simpler of the two.  

We're going to start by creating a parameter.  Once you understand a little bit about parameters, you begin to realize they're super powerful.  They exist outside of your data sets (and are therefore not dependant on the data).  They are leveraged when you create a calculated field and include the parameter to interact with the data.  It's a bit tough to explain the theory - You'll see in a minute what I'm talking about.

The first thing we're going to do is a create a string parameter.  Right click on a measure and select "Create Parameter", the title of which is going to be "Measure Switch".  Now we're going to give the user the ability to switch between three different measures.  Therefore we're going to create a list of three smartly named parameters.  On the left side is the actual value of the parameter (the value that will be referenced in the upcoming calculated field), and the right side is the pretty name that the user clicking on the parameter will see.  Fill it out like so:

Awesome.  Now, as we said before in order to leverage the parameter we have to create a calculated field.  Our calculated field is going to be a statement that looks the parameter, and based on the value will pick a metric to show in the visualization.  Here's what it looks like:

I've chosen to include the aggregation along with the measure.  You don't have to do this, but if you don't, the default is that Tableau will to choose to SUM on yourbehalf - Just FYI.

Now, it's viz time.  Throw "Region" on rows and "Department" columns and now add your newly calculated field "Switched Measures" to columns.  Also add it to the color and text shelf, add some formatting and voila (changes based on the different parameter values):

So there's a simple example of using a case statement and parameter to give the end user more control over a bar chart.  But as we said this can be used on all sorts of visualizations. Maps included.  So we're going to give it whirl as well.  Big thanks to Will Jones and the Interworks team for the spark for this idea.

New sheet.  Change the marks to filled map and double click "State".  We're going to eventually create a dual axis map, so go ahead and Ctrl Click Duplicate Latitude on the rows shelf, and it should be looking like this:

On the marks shelf, click the top map and add the parameter we made "Measure Switch" to the colors shelf.  Now click the map below that one - add the calculated field we made "Switched Measures" to the text and color shelf, change the color to the gray to black palette, and click on color to change the transparency a little more than 50%.  Lastly, right click on that second pill on the rows self and set to dual axis.  Here's what it looks like:

Learn About Tableau

That's it!  Thanks for following along.

Nelson  

Day 10: Big Numbers

In a previous life (ie a college intern) I worked in construction estimation.  I hated it.  I didn't have the patience for it.  In construction estimation a group of people work for a general contractor and pour over architectural plans for weeks in order to add up the cost of building every little piece of a multi-million dollar building.  I distinctly remember days where I would count door knobs for hours. I guess someone had to do it.  All of the work that this team did would ultimately come together on "Bid Day" where the sum of all of those numbers would be added together to arrive at one very large total number.  All that work for ONE BIG NUMBER!

Learn About Tableau

Sometimes, one number is all that matters.  Tableau does data VISUALIZATION.  Trust me, I get it.  But if there's nothing to compare to, no context or other numbers, there's no way to create a visualization of meaning.  That's why sometimes, you just need to show one big number.  

Here are a few examples of this technique:
My Super Bowl Commercials Viz of the Day - 

Andy Kriebel (of Zen Master and Facebook fame) Visualizing 500,000 Page Views - 

This is going to be short and sweet.  Here's the trick.  Each one of those boxes is a sheet is the workbook.  The sheet is just an aggregation of a measure, formated as big text, with smaller text saying what that number is.  Let's hop into Tableau, back to the world of SuperStore Data.  This is going to be quick.

Pick a measure (I'll chose "Sales") and drag it to the Text Shelf.  That's it.  Don't forget to tip your waitress.  

Kidding, but only sorta.  

Now click the test shelf. Click the ellipses ("...") and it pulls up a box you can format:

Select the current text and increase the font size to make it big (I did 20) and go to a new line and write "Sales" smaller and lighter underneath, like this:

Hit ok and now you should have something like this:

Just repeat for each "Big Number" you need to create.  When you bring them on to a dashboard, make sure they are part of the target of your filtering actions so that clicking on something in a visualization will also filter the "Big Number".  

Nice and easy.

Learn About Tableau

And another excuse to reshow the Super Bowl Commercial Viz of the Day as an example:

Learn About Tableau

Thanks for stopping by!

Nelson  

Day 9 (Birthday Edition): Streetview Integration

Well I did it.  I've survived the first 30 years.  In the words of Tim McGraw "I think I’ll take a moment, celebrate my age, The ending of an era and the turning of a page. Now it’s time to focus in on where I go from here - Lord have mercy on my next thirty years"  It's going to be a good day.  

Learn About Tableau

I've been saving this one.  It's a personal favorite that I came up with myself at the end of 2012.  I was working on a parking meter project with the City of San Francisco.  They had the Latitude and Longitude of each of the 27,000 meters in the city.  I wanted to someone at the central office to be able to see what someone out in the field doing collections was seeing, so I came up with a way to show an interactive Google Streetview for each meter.  Once I figured out the correct URL (and parameters in the URL) it was as easy any other URL Dashboard action in Tableau.  I'm pretty convinced that I was chosen to speak at TCC last year because of this little trick.  Sasha and Morgan told me they'd never seen anyone do it before. So, with out further ado, I give you the Google Streetview hack.

I've had enough of Superstore data for a while.  I've gone a grabbed a list of US State Capitals from Wikipedia, and I'm going to use that table as my data source.  We're going sightseeing on state capitals around the country!  Here we go.

 First we're going to make a map showing all the state capitals.  In the data, create a State, City hierarchy (Select them both, right click and select "Create Hierarchy" - make sure state is on top of city).  Double click "State" and you should see dot in each state.  Hover over the State pill on the detail shelf, and hit the plus sign to get to the city level.  I excluded Hawaii and Alaska (not from my bucket list, just from the map), and changed the circles to gold stars - It makes them very capital-like.  It looks like this:

That's pretty much it for our dev on the sheet.  We're now going to make a dashboard.  This sheet will make up the top half and the Streetview will be on the bottom half.  I've size dashboard to be "iPad Portrait" in case you're interested.  I dropped the map in and followed that with a web page container, leaving the URL blank and hit ok.  At the moment it should look like this:

Now for the trick.  Click the map and go up to Dashboard>>Actions>>Add Action>>URL

Side note, I've just spent the last two hours trying to get a workaround from the "new" Google Maps which appears to have been my URL Streetview link.  As such, the only way I could make this trick work is to use Bing.  I never thought I'd see the day.  Thanks a lot Google.

Back to the program.  Tell the action to run on click.  In the URL, paste the following:
http://www.bing.com/maps/?v=2&cp=<Latitude (generated)>~<Longitude (generated)>&lvl=10&sty=x~lat~<Latitude (generated)>~lon~<Longitude (generated)>~alt~296.433~z~30~h~258.3~p~-29.1~pid~5082&app=5082&FORM=LMLTCC

It's not quite the same as the Streetview used to be.  But it is what it is.

You now have the ability to click a city and get the interactive streetview.  It should look like this:

Here's the dashboard.  If anybody can get the streetview to work, I'll buy you a Coke at TCC.

Learn About Tableau

Many thanks - 
Nelson

Day 8: Conditional Formating

I've never met anyone who used Tableau who wasn't also very proficient at Excel.  Both tools can make your life easier in different ways.  One of the things that I love about Excel is how easy it is to create conditional formatting - ie Color cells based on their values.  When done right, all of your problems bubble to the surface very quickly.  In Tableau, this very doable but some what less than intuitive.  So today we dig into Conditional Formating.

Learn About Tableau

One of the things I said at the outset was that I was going to give credit where credit where it was due.  I can remember the first time I saw this beautiful/hideous sheet by Jonathan Drummey (Tableau Zen Master at Southern Maine Medical Center):

I was left feeling something like "I'd never make that, but I'd love to know how he did it.  His workbook is here and it's probably still the best reference/example of how to do this trick.  If this is something you'd like to learn more about, I highly encourage you to download his workbook and dig into it.  BTW - he made this using Tableau 6.0 and 6.1 - just goes to show that a) he's a stud and b) anything's possible with Tableau when you have the following mindset when faced with a problem (from a presentation of his):

Step 1: There's a problem.  Your solution didn't work:

Step 2: Think of another solution: 

I'm going to show two examples.  The first doesn't require the hack and the second one does, but are a version of conditional formating.  

The first example, bar charts, doesn't require the hack.  I include it so you know when the hack is and isn't required (and why).  Let's hop back on to the SuperStore bus and dive in.  I'll throw a couple dimensions (Region and Department) on to the row shelf and a few measures (Sales, Profit and Unit Price) on to the columns shelf.  Its should like something like this:

Doing conditional formating with bar chart is pretty straight forward.  On the left side, on the marks self we have "All" which would do conditioning for "All" of the three measures (we won't touch this) and below it, each of the measures on the columns shelf.  For each of those we're going to click it and add that measure to both the color shelf and text shelf.  It now looks like this (I did also right justified the text):

This is conditional formating.  Nice and simple the colors change independently on each of the columns.  If we had done Measure Names on columns and Measure Values on rows, the colors would have been based on the total sum of the row as one band (like a zebra).  This is a common issue many have when then try to do conditional formating the first time.

Ok, step one is done.  Now for the hack.  Say I want it to look exactly like Excel's conditional formatting, numbers in rows and columns, colored by their values.  We'll channel our inner Zen Master Drummey and dig in.  

In practical terms, we're going to show the colors as one measure behind the text as another measure.  In order to do that we're going to need two placeholders.  These will be two calculated fields - We'll call them "One" (=1.0) and Zero (=0.0).  Once these have been created, duplicate the sheet we just made and remove the measures off the columns shelf.  Here's where we are:

Now we're going to go through a number of steps to get to Sales, our first measure.  The steps are the same for the other two measures, Profit and Unit Price.  Here we go.  Take "Zero" (which we just made, =0.0) and place it on Columns, followed by "One" (=1.0) also on the Columns shelf.  Right click both of them and change them from Measure to Dimension.  Right click "One" and select "Dual Axis", and this is what it should look like:

Click on "All" under Marks and remove Measure Names.  Next click on Zero under Marks - add Sales to the Color Shelf, One to the Size Shelf (Right click and change it to a dimension also well), click on size and drag the slider all the way to the right.  Now, click on One under Marks, change the Mark Type to Text, add Sales to Text, click Color and change it to white.  You should be here:

Let's clean up the axes.  Right click the top one ("One"), and select Edit Axis, change the title to "Sales", go to the next tab 'Tick Marks' and select none for both.  Now for the bottom one ("Zero") - right click and Edit Axis.  Switch to 'Fixed' from 0.0 to 1.0, delete the title, go to the next tab and select none for both (same as before).  When you're done, select the bottom axis and drag it down.  This is the finished product:

Now, do the same thing for the Profit and Unit Price and this is you final product:

Learn About Tableau

And there you have it.  Thanks for playing along.

Nelson