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.