Why Vizioneer?

My photo
Atlanta, Georgia, United States
The "Vizioneer" comes from mashing two words that have shaped my world for most of my adult life - Engineer and [data] Visualizations (or Vizes to those who know what's up). Graduating from first from Oglethorpe University in Atlanta, followed by Georgia Tech with my Bachelors and Masters in Civil Engineering, all of which taught me to think through anything and everything - problem solving, "engineering" solutions, teaching to the "ah ha" moments - is what I love to do. In 2010 that investigative, engineering mindset intersected a job change and a plunge into the world of Data Analysis. In the search for the next great thing I stumbled on to a data visualization and dashboarding product called Tableau software and things just took off. So now I guess you could call me that engineer with the sweet data visualizations - or just "The Vizioneer" :)

In 2013, I joined the incredible team at Slalom, focusing on Tableau and it's been an amazing experience. Recently in 2014, I was honored and humbled to receive Tableau's highest recognition of being named a Tableau Zen Master. Follow along to see what happens next :)

Monday, April 28, 2014

Day 28: Playing Hide and Set with Table Calcs

I love this community.  It's full of smart people trying to learn more and figuring out different ways to do things.  If you've been following the #Tableau30for30 all along (I'd like to thank all 4 of you :)), you know that we've invited three guest bloggers to this point.  Today, given the fact that we are coming to the end of our time together, I was considering adding one last one, this interesting tip from the man, the myth, the legend Peter Gilks of Paint by Numbers (and Slalom New York) fame.  We may have to get the official word, but I think he has more Viz of the Day wins than I have fingers.  He's pretty awesome.  So I got approval to share this blog post tip of his on "The End of Time.... series based calculations" but as I was reading it, I started to think I'd solve the same problem differently - AND - since he closed with the following statement: "If anyone has an alternative approach to this I would love to hear what it is, as with so many things in Tableau there are probably multiple ways to achieve the same goal" I thought it would be fun to share an additional way to do it.  Neither way is wrong but I like variety, and it's why God makes colors.  So today I give you Day 28: the Peter Gilks inspired "Playing Hide and Sets with Table Calcs"

I'm going to let Peter provide the setup here (his words in italics, and we're both using Superstore data):
The situation is this: You've got a time series based table calculation going on that you are interested in showing, something like a running total, YTD total or difference from last month, however you don't want to show all the data - instead you only want to show the latest month.

Your starting position might look something like this below, with year, month, sum of sales, monthly difference in sales and running total.

Now lets say you only want to show the latest month, in this case December 2013. Well the first thing that springs to mind might be to filter, but that messes with the calculations PLUS its not going to automatically update to the latest month when the data updates:

Now the next idea you have might be to HIDE the 'non latest month' data, and that will solve the issue of the filters messing up the table calculations, but its still going to leave you with a problem when you get a new months worth of data you want to automatically show. So this is what you can do....Click to see Peter's solution/keep reading to see mine...

I'm going to right click on Order Date and find something you may not have noticed before - Create Custom Date:

And we'll get this interesting dialog box.  Here we have the ability to tell Tableau to create a calculated field that is the same as Order Date, but at whatever slice of the Dimension we want.  Never used this before?  It really comes in handy in a pinch (which is what we find ourselves in) and saves some coding of calculated fields.  I'm going to use the drop down to select Month/Year, like so:

Tableau now creates a brand new field for us called "Order Date (Month / Year)".

Here comes the trick (in a series of moves).  I'm going to right click on this new field, and select "Create Set".  What I want to do is create a set that only contains the most recent month.  Another way of saying that is the Top 1 Month by Maximum Value.  So we'll head to the last tab and fill it out like so:

Once we click OK, we now have a set called "Last Month".  If we click and drag the set "Last Month" up to the first position on the rows shelf, we get below table and instantly realize that the sort of the In/Out in the table actually makes a difference:

Goes to this when you put In on the bottom (giving us what we want):

Awesome! Now we simply right click on the word "Out" and select "Hide" and right click on the In/Out pill and uncheck "Show Header" and voila:

The cool part is that with both of these solutions (Peter's and mine), as the data gets updated, so will the "Last Month".  The takeaway is that sets can be really helpful when dealing with Table Calcs.  

Hope you enjoyed!  TWO DAYS TO GO!  Many thanks!


No comments:

Post a Comment