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’ using Table Calculations, one portion at a time in a large grid, so I could cross-check everything for accuracy, a bit like people 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-blown table- calc result in the grid, I then dragged each little stepping-stone calc back to the right-side metadata window.  In Tableau, this does two things:  First, the calculation is thereby essentially promoted into full-fledged metadata for the workbook, rather than just being a little creation just in the one worksheet 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 occurred next.

Next morning, the analyst said, “You’re amazing!  And now, can we/you (I) create a filter that hides all data except a specific date of interest?  At this point, after cracking a smile 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, right?”  The response was monumentally simple, “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 at the moment was “Uhhm …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 calculatoin 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 as it thereby becomes, as I mentioned, part of workbook’s metadata, it also becomes part of the underlying queries from Tableau to a data source.  Think of it as an added WHERE clause on a 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 onto a Tableau worksheet and is operated on as part of a calculation process (aka ‘late filtering’).

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!  Anyway, did I get more ‘…you’re amazing!” praise?  Nope.  Was I pleased to deliver something cool and learn a new trick.  You bet!  All good.

 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s