Day 16: KPIs from Table Calcs

You didn't think we'd get through 30 days without talking about Table Calcs did you?  Table Calcs are like Tableau's black box - very powerful, but often misunderstood.  It's one of the quickest ways to discover trends in the data.  The list of built in capabilities and ways to look in to the data is just awesome (we'll show the full list in a minute).  Another thing we talk about all the time with clients is the ability to have the 20 second view of key metrics, often called Key Performance Indicators (KPIs).  Today we'll look at how to create a table calculation and then leverage it to create a KPI.

Learn About Tableau

Before we talk about how to do this in one particular way, let's start with all the different table calc options Tableau makes available.  The way to see this is to place a date field on columns and a measure on the text shelf, right click the measure, and hover over Quick Table Calculation and you'll see all these:

All of these options are really useful in the right circumstance.  Today we're going to look at Year over Year Growth of Profit (from our favorite, Superstore Dataset).  Let's get going.  The set up is really simple - start with Order Date on columns and throw Profit on to the text shelf.  Should look like this:

So that we don't lose track of where we are from a raw numbers perspective we're going to bring Profit back in a second time - put it on details and then right click and go down to Quick Table Calculation>>Year over Year Growth.  Now click on the white space to the left of it and add it also to the text self.  You should now have two rows of the exact same Profit data and should now see this:

We can look and do some mental math and see that this looks spot on.  The cool thing is that we can actual dig into this down to the quarter, month, week... level by simply marching down the date hierarchy. I'm going to take it down one level to quarter and see that this still works:

Not what you expected?  This is like Q1/Q1, similar to the way public companies report earning.  Not what you wanted?  You can change the way the table calc works.  Right click on that pill and hit Edit Table Calculation and you get this box:

To get the quarter by quarter view you we expecting just change the level to Quarter of Order Date.  Change it and you get this:

Now for the trick - here's the easy way to create a KPI from this.Right click and go back to edit the table calc.  On the bottom left you see a button that says Customize - click it and it brings up the calculated field that Tableau worte in order to produce the table calculation you saw.  Here's the fun part.  Copy it and kill the window.  Create a new calculated field and paste it in there.

At the end of the calculation add ">0".  Now, write an if statement, with the formula as the boolean, followed by "then "Up" else "Down" end".  The whole thing should look like this:

This field will now go on to the shape shelf (one you change the mark type to shapes).  Click on the shape legend and take advantage of the KPI shapes that are already in Tableau.  I went with green circles and red diamonds.

After a little formatting we get this little table:

If we hid the bottom row, we'd have the beginnings of a pretty sharp KPI dashboard.  And now you know how to make it happen.

Learn About Tableau

Thanks for playing along!

Nelson

Day 15: Custom Color Palettes

One of the things I harp on when it comes to designing something for a client (or anyone really), is context.  Ryan Robitaille (of Tableau Zen Master and now Facebook fame) wrote one of my favorite blog posts of all time last October during Tableau Design Month.  It's an awesome and hilarious read, but focused on one point - you're dashboard better make sense contextually, or it's going to be a dud (that's the nice version of his headline "If content is king, then CONTEXT IS GOD").  One of the easiest ways of doing that is to be thoughtful with color.  Today's quick trick focuses on how to get the right colors every time.

Learn About Tableau

It's amazing to see the brand recognition that some companies have.  The most basic piece of a brand is it's logo - nothing more than text, colors, and images - that become recognized everywhere.  















Just looking at some of the bigger Atlanta brands, color is amazingly impactful - think of Coca-Cola red, Home Depot orange, UPS brown ("what can brown do for you?" - see?).  I actually found a website that profiles the color palettes of brands:

But you can even use something as simple as a browser add-in to pick up colors online.  I use this snazzy one called Colorzilla as a Chrome Extension.  It's baller.

So what am I getting at?  Color matters! A lot.  I'm all about not only creating insightful and functional dashboards, but also ones that have look and feel that match their environment.  That's why you have to get color right.  Getting context right is what drives end user adoption - and if you don't get adoption, it's as if you didn't make the viz in the first place (because the same number of people are using it - zero).

So here's how you nail your colors every time.  Tableau has a little gem of an article that outlines how you write some super simple xml (If I can do it, you can do it), in a blank little file called Preferences that's sitting in your "My Tableau Repository".

The article outlines how you can write in three different types of color palettes: categorical color palette (individual colors), a sequential color palette (from your color fading to white), and custom diverging color palette (from one color to another, think red to green (if it's Christmas time, then never again, right Stephen Few?)).

Here are examples of an NBA color palette I used for some vizzes a while back (yes, it really is this easy):
- Categorical color palette (red, blue, white):
   <color-palette name="NBA Test" type="regular">
      <color>#D53D52</color>
      <color>#0B68AD</color>
      <color>#FFFFFF</color>
    </color-palette>

- Diverging color palette (red to blue, think hot to cold)
    <color-palette name="NBA Sequential" type="ordered-sequential">
      <color>#D53D52</color>
      <color>#0B68AD</color>
    </color-palette>

Load up that preferences file with all sorts of great color schemes.  I'd love to see everything that Kelley Martin has in there.

Hope that was helpful.  Thanks for hanging out.  Hasta manana!
Nelson

Day 14: Filter Multiple Dates with Parameters

Today, April 14th is a big date in the Davis family.  Seven years ago we walked down the aisle and made it official.  She became my better half and I got to continue to tell her nerdy jokes that she still pretends are funny (bless her heart).  (And everyone said 'awwww').  

Dates.  Today's a big one for us.  I'm sure you have some big ones too.  I also bet the same thing can be said of your data (see that transition???).  I love looking at trends over time and some of the filtering that you can do in Tableau is pretty awesome.  Right up to the point where you want to be able to filter multiple data sources based on date filter.  That becomes either very complicated or impossible based on your skill level.  Today we're going to look at how you can still give the user almost complete control over date filtering even when your dates are coming from multiple sources.

Learn About Tableau

I'll give you the actual set up of where I had to come up with this one.  I've mentioned before that my first assignment after joining Slalom was to create a demo dashboard based on financial data.   I created about seven or eight dashboards that all tied together and there were multiple data sources, so filtering the data based on date from just one source was inadequate.  I needed a way for the user to set their date filter a have the entire workbook update.  I knew the only way to do it was with a parameter, but how would I tie it all back together?  This was a bit of an advanced move but three parameters and two calculated fields later, I was cooking with hot oil.  So who's ready?  Let's dig in.

We're going to make some assumptions:
- You have multiple data sets
- They all have dates that relate the time periods to each other
- I'll show you what to do on one of these data sets/sheets and you can iterate it as many times as needed (ie in each of your data sets).

Let me explain what the end product is going to be and we'll work backward.  We're going to create a functional version of what you see below (which practically would show the four quarters of 2013):

Above you see a date filter where the user inputs three things (each one is a parameter, going in order left to right):
- The first is the number of time periods.  An integer that must be greater than 0.
- What the time period unit actually is - weeks, months, quarters or years.  This is going to come from a string.
- What time period cut off is - I chose to give the ability to cut off at the end of each quarter, or to have no cut off and use "As of Today" as an option.  I chose to do this as a string but it's probably possible do it as a date parameter as well.

So let's make each one.  First the number of time periods - it's just an integer that has to be greater than 1, like this:

Ok, next - the different units of time.  We do this as a string with the time periods written out (lower case, in the singular form), like this:

Here's the last one, the cut off date.  I've done this as a string, but it's probably doable as a date type (the issue I run in to is passing in Today() in here as a date - not sure that it's fixable using this as a date type parameter, thus I go with the string type).  Here's what it looks like (I show both YY and YYYY to show that they both work and because I'm a goofball):

So now all of our parameters are made.  We now have to create two very important calculated fields.  And here's the thing - both of these field must reside in ALL of you datasets that have dates.  That's right ALL of them.  Probably a good time to mention that Day 57 of #Tableau30for30 would for have been on Copying Calculated fields from one data source to another, but alas there's only 30 days in April (thank the Lord).  So I'll let you figure that one out.

The first calculated field is a case statement that switches that cut off date string parameter to a date type.  Here's what it looks like:

case [As of Date Param]
when "Today()" then Today()
else DATE( [As of Date Param] )
end

The other calculated field is a boolean that brings everything together.  I wish I could say that this was really simple, but the first time I wrote it out and got it working, it took a couple hours to get just right.  The idea is to show everything going back to an N number of time periods through the cut off date.  I could try to explain the whole thing, but that would take a while and you would feel like I was insulting your intelligence to think you couldn't see this and understand it.  And the last thing I'd ever want to do is insult the intelligence of a blog reader.  I like you guys.  And your friends... who you just told to subscribe...  Oh, yeah, back to the calculated field ([Time Period] is my date field from my data):

DATETRUNC([Time Period - Last n Unit], 
dateadd([Time Period - Last n Unit],(-1*[Time Period - Last n Number]+1),
datetrunc([Time Period - Last n Unit], [As of Date] )) )<=[Time Period]

and [As of Date]>=[Time Period]

Now you got it!  And again, this has to go in to every data source (and needs to be modified if the name of your date field changes).  This calculated field, the one I called "Date Filter - Global" also needs to be added to EVERY SINGLE ONE of your date sensitive sheets in the filter shelf AND added to context EVERY SINGLE TIME.

Is that a pain? Sorta, but you needed a way to filter dates, and once this works it's beyond awesome.  

Lastly, you'll need to include those three parameters in each of your dashboards that are going to leverage this solution.  To end where we began, this is how mine looks:

I hope that helps.  If so, drop me a line and let me know.  See you tomorrow, we'll be half way home.  Many thanks!
Nelson

Day 13: Blending as a filter

I would argue (and win) that blending is either one of the best or worst features of Tableau.  Sometimes it's brilliant and works exactly how you need it to (such as my current project I'm working on here in Atlanta, which wouldn't be possible without blending) and other times (like when you need to do a row level join between two data sources of different types (and you don't have Alteryx)) it feels half baked and inadequate.  But since I'm a glass half full kinda guy, we're going to look at a trick to leverage blending when your normal filtering doesn't work the way you want it to.

Learn About Tableau

For those of you with much experience in Tableau, I'm going to go ahead and answer the voice inside your head that as you read this will say, "That's not the only way to do that".  That's correct.  There's many ways to do this trick.  There's always many ways to do a trick - Pick a card, any card (see what I mean?).  But this is a simple way to handle tricky filtering.

Here's the set up.  I have population data on all fifty states in the in the US.  I want to show it in a map, but I only want to show the lower 48 (it fills up the window better and who really cares what people it Hawaii think anyway!?!?).  Problem is when I click to filter and then unclick to un-filter to show all, the state filter where I excluded Alaska and Hawaii also disappears and the map returns to show everything, and I'm left with a killer view of the Pacific Ocean right in the middle of my window.  What I want to happen is that my exclusion of Alaska and Hawaii will remain, regardless of the filtering being done as a result of actions on the dashboard.  We have a dashboard that looks like this (showing Alaska and Hawaii in the bar chart but excluded in the map):

So here's what I've seen done before (and then we're going to do a trick): Once you have the visualization(s)/dashboard created, right click on your data source and select duplicate.

Now, in the duplicated data source bring "State" on to the filters shelf of the sheet with the map.  Hit the "Exclude" check box, and check off Alaska and Hawaii.  

Now, the the map should update and show only the lower 48.  Now right click on the filter in the filters shelf and click Apply to Worksheets>>Selected Worksheets, and now check off all the other sheets in the dashboard you wish to apply this filter.

Now when you select a state and then deselect it, you keep only the lower 48.  Pretty neat huh?

"But Nelson, I'm looking at 30 Million rows of data.  Do I really need to duplicate the whole thing?"  Of course not!  Here comes today's trick.  Instead of duplicating the entire primary data source, create a new sheet with just the field you want to do the blend filtering on.  For the state example it would look like this:

Now, select Worksheet>>Copy>>Crosstab.  You now have this data sitting on your clipboard.  Simple hit CTRL+V and Tableau will paste that data into your workbook as a data source - you'll see it up there at the top left, starts with "Clipboard...."

The great thing is how small this data is.  It's only the number of distinct rows in your data (So 50 rows for fifty states :)).  Now, I'll use this clipboard data as my blended data set and follow the same steps as above.  

Now that's a trick.  I've posted two versions of the same dashboard, one using the blended data as the filter, the other with a normal filter, so you can see the issue I described with the normal filter:

Learn About Tableau

Thanks as always for stopping by.  
Nelson

Day 12: Formula Measure and Describe

I'm probably the only person that didn't know about this one.  I should be mad y'all didn't tell me.  But I'm finding the "Describe" button to be super useful.  What's that? What's that you say?  You've never heard of the "Describe" button either? Well, like many of the other tricks, it's money in the bank when you need it.

Learn About Tableau

It doesn't matter what data set you use on this one.  Today we're talking about a concept and a trick that makes the concept easier.  Here's the deal.  I've been doing some work recently where I have to make a table that contains a bunch of different metrics.  Problem is they all have different filters and look at different fields (and again they all have to be on the same table).  Given this set up, using the Filters shelf isn't going to work as it filters everything the same way.  Therefore, you're going to have code in the filters into the calculated field into the measure. Stop me if you've heard this on before.

So as I write these equations, I need to know what to filter - ie what's in the data, and that's where our trick comes into play.  Here's the example.  This particular metric (the one I'm making up as I type) requires I divide Furniture sales in the West Region by the number of Technology units sold in the East Region.  So what does that look like as a calculated field?

Create a calculated field.  We'll call it "Weird Average" and we're going to write an if statement that has booleans (T/F) inside of the "if" part and a measure in the "then" part of the statement.  Because we're looking at two different numbers, we'll have to do the if/then twice, and aggregate each one.

As I begin to write this out, I forget what's actually inside of "Region" - is it West or Western?  And here's the trick - there's a cute little button called "Describe..."

When you click it, you see a bunch of information, but you don't immediately see anything that's helpful.  Then there's one more magical button called "Load".

I hit that and here's the awesome thing - now I can see exactly what's inside of this data field.  Turns out it is called "West" and not "Western".  I can select it, hit the copy button, close out and paste it into my formula.

And this is what it looks like:

This is really helpful for spelling and abbreviations that might be in your data.  I discovered this a few weeks ago, and now I'm using it constantly.  Here's what that full formula looks like (remember - I divide Furniture sales in the West Region by the number of Technology units sold in the East Region):

Ta-da!  Fun with metrics!  Hope you enjoyed!

Many thanks -
Nelson