A recent customer gave me a chance to exercise Tableau features, on both the Server and Desktop.
Although this write-up is focussed more on calculation than visualization, it also demonstrates my approach to visualizing complex data with a simple, impossible to misunderstand, presentation. My customer wanted visualizations that would bring simplicity to a rather complex calculation. The combination of value-labeled drop lines set to ‘on hover’ pointing left-vs-right across the sync’d dual-axes, and simple colors makes the end-user’s job of answering the following question effortless: “How did upsells do today, hour by hour (orange bars), in relation to our benchmark 4-week moving average for those same hours of the day? (blue line)
The Approach to the Underlying Calculation:
Because the source data was not transformed sufficiently to directly surface crucial data, I used Tableau to perform a series of calculations. As a move towards standardizing this process, I elected to write basic table calculations, validate them for accuracy in a cross-tabular display, and then build more targeted calcs on top of the basic ones, a reliable approach that Tableau’s calculation feature set supports well and which helps to eliminate duplicative sets of ‘soup-to-nuts’ calculations. If a building-block calc is wrong, I want to be able to fix it once, not fourteen times.
The calculation required that a current hourly value (up-sells from free to paid service) be compared (visually, of course) with a moving average of its corresponding values at that same hour, each week 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 like people often do it in Excel. Because I used table calculations, time-intelligence, and the aforementioned calcs-on-calcs, it was completed in one caffeine-fueled evening. 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 and named it. In Tableau, this does two things: First, dragging back and naming it essentially promoted it as full-fledged metadata for the workbook, rather than just being a creation just in one worksheet. 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 (meaning I) create a filter that hides all data except a specific date of interest? At this point, after briefly reveling in 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 have data underlying the 4 week moving average you requested.” The response was monumentally simple, “Uhhm, but I want to see the 4 week average. Can you do it, Mr. Tableau”? Since Mr. Tableau’s attempt at “platform-limitation as logic-limitation’ argument failed, all I could manage at the moment was “Okay, I’ll look into it.”
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 section and naming it. Most importantly, and although I neglected to capture a screenshot of the ‘late filtered’ solution we created, it works like this. In Tableau, a table calculation is limited to the worksheet wherein it was created, and thus has no effect on Tableau’s query on a data source, which happens to be good in this case. When a table that filters out older data is dragged to the metadata tab and named, then as I mentioned, it becomes part of workbook’s metadata, and thereby becomes part of the underlying query 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, as well as an appropriate viz, made up the secret sauce. All I had to do was remove the named Table Calc from the metadata and re-build it as a plain-old table calc. This made me laugh at myself, because notwithstanding my aforesaid failed ‘technology-limitation as logic-limitation’ impulse, I was able, with Tableau 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, but I’ve learned not to expect it anyway. Was I pleased to deliver something cool and learn a new trick. You bet! All good.