Tableau’s Order Of Operations Or What Happens When And How
One of my first blog posts as an Analytics Consultant in the Business Intelligence and Tableau space was about Tableau’s data lifecycle. The blog post was meant to be a trilogy, but as life and work have it, other tasks soon filled up my schedule and I never wrapped it up. Bummer.
Fast forward a few years and this topic still fascinates me: what operations happen in which order, how does that help me and others understand Tableau’s data lifecycle, and how can I use that to my benefit?
I deliver quite a few Tableau Workshops, and next to all the material and exercises I always wished I had a precise and concise one pager that clearly shows why you cannot mix aggregates and non-aggregates in calculated fields (and how to circumnavigate that issue), why level of detail expressions essentially are non-aggregates after they have been computed, and why cosmetic table calculation filters are a thing.
How great would it be to have a poster for fresh Tableau graduates, next to a flurry of amazing online resources, for their data journey. Some honorable folks, namely David Weaver and Joe Mako have been working on a great overview, some of which I have adopted. But I felt it was lacking a key ingredient: data visualisation.
What better way to quickly get when a certain filter is applied, what happens to the length of your data set, and due to what operations your data set grows in width? All that was left to do was to add a bunch of sample calculations that mix various operations, and using colour coding to tie them back to the order of operations.
So here it is, produced for the big screen (or printed on DIN A3 paper): The Tableau Order Of Operations Cheat Sheet!
You read the upper half from left to right, and you can see how your data set shrinks vertically with each new filter that is being applied, colour coded in red. You can also observe how your data set grows in width with each new calculation you add, colour coded in grey, yellow, beige, blue and white. At one point the data set shrinks further, as we enter the realm of summary data, using aggregations. Soon after that blending kicks in, and an additional orange data set appears. Easy.
The lower half are sample calculations, colour coded to tie them back to the individual stages of the order of operations. See how row level calculations and simple aggregates are mixed? Hopefully this will be a big help for people having just started with Tableau and more often than not are running into the “cannot mix aggregates and non-aggregates” error.
Enjoy and let me know what you think in the comment section below!