Fun with Calculations in Tableau

Tableau Same Hr 4 Wks Prior 1

My most recent customer gave me a chance to exercise Tableau Version 9’s features, for both the Server and Desktop Professional.

Early on, an analyst asked me to do something rather interesting with calculations in a workbook.  Because the source data was not transformed sufficiently to directly surface crucial data, Tableau was being used to perform fundamental calculations.  As a move towards standardizing this process, we elected to write basic calculations, validate them, and then build more targeted calculations on the basic ones, which is a very reasonable approach that Tableau’s calculation feature set supports well and which helps to eliminate redundant set of ‘soup-to-nuts’ calculations.  If it’s wrong, we want to be able to fix it once, not seventeen times, right?

The calculation required that a current hourly value be compared (visually, of course) with a moving average of its corresponding values each week, at that hour, for the prior 4 weeks.  The screenshot above shows the end result.  Without going into the calc-trivia, I accomplished it with ‘belt-and-suspenders’, one portion at a time in a large grid where I could cross-check everything for accuracy, a bit like we would do it in Excel.  Because I used table calculations and time-intelligence, the calc was done one (late, caffeine-fueled) evening after some wrangling.  Once validating the full-blow calc result in the grid, I dragged each little mini-calc back to the right-side metadata window.  In Tableau, this does two things:  First, the calculations is thereby essentially promoted into full-fledged metadata for the workbook, rather than just being a little twist-flip in the data grid itself.  At various times, I recall copy-pasting one calc’s formula to another, to be honest.  Anyway, before I get to the second effect, I’ll tell you what was requested next.

The analyst said, “You’re amazing!  Now, can we/you create a filter that hides all data except a specific date of interest?  At this point, after smiling about the ‘Amazing’ part, I got testy (think: nervous), saying “Well, if we filter out the older data, it will be gone, and we won’t see the 4 week moving average.”  The response was essentially, “Well, I want to see the 4 week average. What can we do”.  Since my attempted “platform-limitation as logic-limitation’ argument failed, all I could manage was “Okay …I’ll check.”

Googling madly, I luckily stumbled upon the Tableau term, ‘late filtering’.  It sounded tasty, it was delicious, and it leads to the second effect of aforesaid dragging / dropping a table calc back to the metadata menu.  Most importantly, and although I neglected to capture a screenshot of the ‘late filtered’ solution we created, it works like this.  In Tableau, if a Table calculation that filters out older data is dragged to the metadata tab (and thus needs to be named), then it becomes part of the underlying query to a data source.  Think of it as an added WHERE clause on the SELECT statement.  As such, if the query to the data source eliminated values prior to a given datetime, then of course, the underlying data for the 4 week moving average would be lost, right?  Well, on the flip-side, if a table calc is NOT named (brought into metadata), it does NOT become part of the query, but instead is processed AFTER the data is brought into the Tableau calculation process.

So, late filtering was the secret sauce, which made me laugh at myself, because notwithstanding my aforesaid failed ‘technology-limitation as logic-limitation’ impulse, the Tableau platform was able to support this unique request very elegantly and, here’s the funny part, with a very techno-platform-specific method.

Silly me!

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s