Tableau Tip Tuesday: Show What Isn’t in the Data
I recently came across this post that highlights the importance of seeing what isn’t in the data: http://blogs.hbr.org/2014/05/visualizing-zero-how-to-show-something-with-nothing/
In Tableau, rows, columns and marks are defined by records of data. That means that most of the time, you are limited to showing what is in the data. But there are a few options for showing what isn’t.
- Show missing rows or columns. From the menu select Advanced > Table Layout > Show Missing Rows / Columns. What this will do is show headers for the domain of values present for the discrete field defining the header. This is context sensitive, so you can limit the range with a context filter.
With “Show Empty Rows” on, every category is shown even though only a few have values based on filtering to a single department for a single city
When the Department filter is added to the context, only categories within that department are shown. However, they are shown even if other filters would normally exclude them.
- Turn on “Show Missing Values” for dates and bins. (Right click or use the drop down menu on the field in the view and select Show Missing Values). Since Tableau knows the min/max values and increments for a date or bin that defines a Row/Column header, it can fill in missing values and thus show you what’s missing.
Without the “Show Missing Values” option, only three dates are shown.
With “Show Missing Values” turned on, we see the gaps in time between sales of the item.
- Pad the data on the back-end. You can supply missing values by joining or unioning to give you the complete domain of a dimension.
Here we want to ensure that “Dallas” and “Seattle” always exist in the data. We can combine this with “Show Empty Rows / Columns” to always see a row / column for Dallas and Seattle.
- Pad the data in Tableau. This one is harder as it requires an understanding of how different table calculations interact with dimensions on various shelves. But sometimes, it’s your only option (e.g. you can’t touch your data source because it’s cloud based or locked down). If you are interested in knowing more, do a search for Tableau along with the key words “Data Densification”, “Domain Padding” or “Domain Completion”. Also, check out this great video featuring Joe Mako: http://vimeo.com/107354333
Check back each Tuesday for another Tableau Tip
Joshua Milligan is a five-time Tableau Zen Master. His passion is training, mentoring, and helping people gain insights and make decisions based on their data through data visualization using Tableau.
this union solution doesn’t work when any filter is applied on the respective table. Any further solution for same
Sanket,
I’m not certain I quite follow. Only a context filter should remove rows/columns when the Show Empty Rows/Columns option is checked. Do you have an example?
Best Regards,
Joshua
This SQL doesn’t seem to work for me, apparently because the two special SELECT clauses do not have a FROM part.