As the title suggests, this nifty trick has the dual purpose of allowing you to trigger sheet swaps using Dashboard Actions and, when two data sources contain different levels of Granularity, it lets you analyse at both levels (the Granularity of the Primary and Secondary data source). This is the ‘illusion of a Full Outer Join’…All seems a little cryptic and techie. Let’s run through an example…
Background…(When/Why to use this trick)
This example relies on having two ‘Blended’ data sources, so let’s first look at why/when to blend, as opposed to joining. One use-case is when the data resides in different ‘schema’ (a schema is a single Excel workbook, where the tabs form a single schema, or for text files if they are in a single folder, or for a database a ‘schema’ is…well a schema!). I see this as a fringe case, as it’s fairly easy to get data into a single schema, and do a proper join if appropriate. The main use case, IMHO, for data blending, and taking full advantage of Tableau’s ‘aggregate-and-join’ functionality, is when the 2 different data sources have different levels of granularity. Granularity of data is a very important, and misunderstood, concept and one of my faves, so more to come on that subject (as well as ‘Blending vs. Joins’) in future posts. However to show you what I mean, and to demonstrate the sheet swap trick, I’m going to use a (very common) retail example, looking at stock of a product across the store estate and in depots/warehouses.
Suppliers and Retailers, alike, are very interested in Stock (having the right things, in the right place, at the right time, at the right amount is 90% of retail!). The stock in the system are held in two places, the stores and the depots. Very often the data for Stores (Retail), and Depot Stock (Supply Chain) reside in different systems, and more importantly at different levels of granularity. Below is a sample from the 2 data-sets we’re going to use;
Store Data, with Granularity by Day, Product
Depot Data, with Granularity Day,Product, Depot Name
So first I want to build up the views to go in my dashboard. I want to create an interactive, and analytical dashboard to investigate stock (both store and depot). We will look at this at product level, and also show the stock level by depot, and a time-series view looking at stock (both store and depot) over the last 30 days. However what I want to happen is that if a user clicks on a depot, the Product and Time Series charts change to show the Stock in the depot excluding the Store Stock (as Store Stock is not relevant at a Depot Level). To achieve this I create 2 versions of the Product Level and Time Series Charts (one with Store and Depot stock and one with just the Depot stock), with the aim of ‘Switching’ between these views as the user clicks on a Depot. Due to the different levels of granularity, we can’t really join these data sources together (without something inelegant, inefficient, and non-scalable), so blending is the answer. The best thing about data blending in Tableau is that it aggregates the data in the secondary source, and then joins this aggregated figure back to the primary source at the common level (again more about this in a dedicated post on the subject).
Setting Up the Worksheets
First, I add a couple of calculations I’ll need. In both data sources for the Product and Depot view I only want the Stock from the last day (as a sum of stock over all days doesn’t mean anything). So first I create a LOD calculation to get the last date in my data set (called ‘Max Date LoD’)
{MAX([Date])}
NB. If you are using FIXED, and over all data (i.e. no dimension splits) you can exclude the word FIXED and just use the {} to let Tableau know it’s a FIXED LoD Calculation.
I then use this ‘Max Date’ to create a field which only contains the Stock for the last day. I’ve called this ‘Store Stock Last Day’ (Store Data) and ‘Depot Stock Last Day’ (Depot Data) and has the formulas
IIF([Date] = [Max Date LoD],[Store Stock],0)
and
IIF([Date] = [Max Date LoD],[Depot Stock],0)
I then set up the Product (Store and Depot Stock) as per the below image
and the Depot only stock version as per the below image
Remember that we’ll be switching between these 2, depending on an Action filter on Depot. This way we can see total stock in estate (the top bar-chart), but by clicking a depot can then view the stock for that depot.
We then set up a similar time-series view, again with the 2 versions
It’s worth noting at this point that Tableau has automatically Blended the 2 views (on the charts with both Store and Depot stock) on the level of details in the Viz. So the Product chart is only Blended on product, and the time-series on product and date. As the measure on the product chart is calculated, as just the stock on the last date, we don’t need the blend on date here…more on this in our future post on Blending.
We then also set up the Depot Stock view, which will act as our selector. This is set up as per the below
So now we have all our sheets set up, we can arrange them in a dashboard as per the below image. Notice that (like a parameter driven Sheet Swap) we place the sheets to be swapped in the same container, and set them both to ‘Fit Entire View’ or ‘Fit Width’ or ‘Fit Height’
Setting up the Sheet Swap on Dashboard Action
First we set up 2 fields in the Depot Data; One called ‘1One’ (with the formula: 1) and one called ‘0Zero’ (with the formula: 0). Then convert these both to dimensions (btw this still works if we don’t change them to dimensions, but I’m a bit particular!).
Now, for the 2 Sheets which contain both the Store and Depot Stock (‘Last Day DepotStock by SKU’ and ‘Time Series DepotStore’), drag the ‘1One’ field into the Viz Level of Detail (below is shown for time-series, but we do the same for the product level)
Next we drag the ‘0Zero’ field into the ‘Last Day Stock by Depot’ tab
…hopefully you can start to see how this is going to work!
We now set up our Action filters on the dashboard in a very specific way, taking advantage of the fact that ‘1One’ and ‘0Zero’ can never match.
First we set up the Action Filter that, on selection of a Depot, will ‘collapse’ the Store/Depot sheets. This is set up in the following way (…this is actually the ‘Inverse’ of the ‘Clearing Selection will Exclude All Values’…and will act as ‘Selecting will Exclude All Values’)
As you can see we have manually changed the ‘Selected field’ to pass to ‘0Zero’ and ‘1One’. This has the affect of ‘collapsing’ the target sheets as 0 never equals 1 (…unless we want to get philosophical about things!!)
Now we add in the Action filter that will ‘collapse’ the Depot Stock only sheets (‘Time Series Depot’ and ‘Last Day Depot by SKU’), when a depot isn’t selected. This will also pass the Depot name to these sheets on selection so we can see the stock, for a selected Depot. Below is the set up for this (notice the ‘Exclude on Clearing’ is ticked).
I’ve then set up a further action so we can use the Product Bar charts to filter any of the views by Product.
Final Thoughts
We now have a sheet swap triggered by a Dashboard action, and the ‘Illusion of a Full Outer Join on Blended Data’…By this I mean that we have Depot Level stock in one data source, but store level in the other. We can however look at total stock in the estate (be it Depot or Store), and we can also get the extra level of Granularity (present in the Depot data, being down by SKU/Day/Depot) at the same time. I have used this trick for looking at Wastage too (for those not immersed in the ‘exciting’ world of perishable retail!…’Waste’ is the value of stock thrown away, or reduced, due to it going out of life, or being damaged). For Waste, the data at store level often just gives an total amount, whereas a separate data source (usually from internal store system) breaks this down by ‘Waste type’ (Reduced, Thrown Away, Damaged…etc.), so using this technique we can home in on the biggest drivers of waste, category/product/store-wise, and then see what type of wastage has driven it (which in itself is crucial for finding the cause, and so a solution, to the problem).
Hope you find this useful, and interested to see other use-cases using this (or a derivation of) this technique.
You can find, and download, the example workbook we’ve built here at
https://public.tableau.com/profile/simon.r5129#!/vizhome/SheetSwapusingActionFilters/Dashboard1
The Data Animators 