Tableau opened up a whole new world of possibilities when they introduced Data Blending in version 6.0. Not only did this allow users to combine data sources without relying on SQL or a database developer (although joins can be more efficient), you could now combine data sources across databases or data types! Have an integer ID in a database source? Create an Excel document mapping the ID and description and blend the two together!
Setting up blending is incredibly straightforward, but getting it to work the way you expect isn’t always so easy. Blending on fields like Medium, Campaign, or Company usually works the first time (assuming spelling and capitalization matches), and it’s normally easy to tell if something goes wrong. But blending on date is another matter. Try to blend a date field with a datetime field? Nope. Have weekly data in your primary source and daily in another? Not going to work.
After much frustration and experimentation, I can summarize the key to blending with dates in one thought:
The time period you blend on must be the same as the time period used in the worksheet.
For example, if you’re showing weekly data both data sources should have date data at a weekly level. Showing monthly? The blend fields should be monthly. Showing a date range using date pickers? Blend on a calculated field that determines if a date is in the range.
Here are some details based on the time period used in a view:
Daily:
Create calculated fields in both data sources called MDY as datetrunc(‘day’, [date])
Note: Make sure every date is in your primary data source!
Weekly:
Create calculated fields in both data sources called WY as datetrunc(‘week’, [date])
Monthly:
Create calculated fields in both data sources called MY as datetrunc(‘month’, [date])
Date Range:
Create calculated fields in both data sources called B.DateRange (B as in Blend – you probably won’t use this field for anything else and the prefix helps you remember to use it) as [date] >=[Start Date] and [date]<=[End Date]