Duplicating Datasources for Fun and Profit

Posted on Posted in Uncategorized
This short post was inspired by a Tableau Forum post forwarded to me by Wilson yesterday: https://community.tableau.com/thread/239017

To Summarize: I want to select a month in a time series view and use it to see that month in comparison to other months.

This sounds simple but because of the way filter actions work it can be tough to achieve. Basically, a filter action restricts the target views to only the data contained in the selection you made – that means when we pick one month, we lose the context required to do month-over-month comparisons.

There are some ways re-design the dashboard so it does what we want –

  1. We could require the user to select two months (by clicking-and-dragging or holding down the ctrl key). That works, but it could be a little confusing for our end user.
  2. Or, we could use quick filters or parameters to control the target view in a different way than by using filter actions. Clear directions on the dashboard would make this easy to use. But it might not be as delightful as clicking directly on the visualization.
  3. Finally, we could write an L.O.D. expression – the FIXED command operates before filters on the target view, meaning if we’re clever we can probably find a way to dig out the prior month using this. But let’s assume I’m not Wilson Po and therefore I’m not a throbbing brain of calculation competency.
Let’s assume we want the exact design outlined by Andres in the post I linked above. There’s a quick solution that all Tableau dashboard designers should know which will help: Duplicating the data source is a great way to manipulate your data in situations like this, and Tableaus’ Data Blending feature gives you a way of breaking the filter action logic that makes this type of interaction difficult.

Duplicating the data source is easy. Just right-click on your data source and select duplicate. This creates a second copy of the metadata associated with the data you are using. It doesn’t duplicate the data itself. You can manipulate this data source separately – meaning you can rename fields, add calculations and parameters, groups and sets, etc, without it impacting the work you’ve done in your original data source. It also won’t affect the views you’ve already built, but you can always substitute your new data source for your old one in that same right-click menu.

Here’s why it’s helpful in this situation: We can combine these two data sources by doing a join. By joining the Month field from our original datasource to the Prior Month from our duplicated data source, we can force the second data source to show us both the Current and Prior month of Sales. Essentially, we’re turning this data structure…

Month Sales
January 34
February 87
March 23
…into this data structure:

Month Prior Month Sales Sales
January 34
February January 87 34
March February 23 87

All we need is to create a join key that gets the previous month. We can do this with Tableau’s calculated fields. I think the best way is to use Dateadd:

I renamed the original date column of “Month” to “Month (orig)” and called my calculated field “Month” to make it easier to data blend, since Tableau will automatically find like-named fields between data sources for data blending purposes. But you don’t have to do that if you don’t want to. You can go to Data -> Edit Relationships to blend your calculated field in your duplicated data source with the data field in the original source.

A couple of things you should be aware of:

  • Note that this approach would also work with the Cross-Datasource Joins capability that was introduced in Tableau v10.0 and the Join Calculations functionality that was released in Tableau v10.3. I think Data Blending is easier to set up.
  • For more complex visualizations or data restructuring challenges, a join could change your data in strange ways. For example, you could duplicate the data in such a way that your Sales totals aren’t right. But for this example, it works great.
  • On the other hand, a cross-datasource join will probably perform better on complex views. If I had 1000 products instead of 5 – it might be worth doing a cross-datasource join because the queries Tableau sends to the underlying data will likely be simpler, and I can extract the datasource if I want.
But the reason I like this approach so much is that it’s easy. One simple calc, rename a couple fields and you’re off and running.

Leave a Reply

Your email address will not be published. Required fields are marked *