Should Tableau data blending on a date field be causing problems you have come to the right place. I had to blend data between an Excel file and a Tableau data extract and it didn’t work.
I was blending on two fields:
- A text field called Metric, with had the same field name in both datasets. Tableau picked this up automatically, the chain symbol next to the dimension name.
- A date field. Called Date in the spreadsheet but InvoiceDate in the Tableau extract.
Tableau was clever enough to join the Metric fields automatically.
However, as the field names differed, I had to manually create the date fields relationship.
To create a relationship, on the Tableau menu bar choose Data—Edit Relationships. I thought it would be simply telling Tableau to join where Date = InvoiceDate.
Unfortunately, it was not that simple.
How to blend dates in Tableau
If you unfamiliar with data blending it’s best if I go back to the beginning.
Data blending is a way to join two separate datasets, similar to a Join in database language.
For example, you could have website visit data in one source and order data in another which you want to display together to show a visit to order conversion rate over time. Tableau have created a good video guide on data blending.
Tableau automatically “guesses” which fields are related, I think by using the field names.
Where the field names and data types match Tableau will assume they are related.
To edit/add/delete Tableau’s automatic relationships go to Data – Edit Relationships and select Custom.
There you can add, edit or remove any of the joins.
My issue was joining the date fields. Initially I had it set up with one line where Date = InvoiceDate. The automatic Metric = Metric is also there.
Unfortunately this didn’t work.
Blend dates using the date parts in Tableau
After much head-scratching, and trial and error, I eventually found the answer.
Open up the date field – it automatically presents as a hierarchy – and join the corresponding date hierarchy fields on the same date part.
For example join the Quarter to Quarter, Year to Year, etc.
When selecting part of the date in the primary data source the only relationship option available in the secondary data source is the corresponding date part. In this case it’s the MONTH.
For my join to work I had to join the DAY, MDY, MONTH and YEAR although my data was stored at a daily level.
Finally the relationships were like this:
You will know when the joins are correct and active. Tableau puts an orange chain links symbol to signify there’s an active relationship.