Tableau Data Blending With Dates

//

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:

  1. 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.
  2. 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.

Tableau relationships window with fields related

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.

date fields expanded to date parts to create relationships in Tableau data blend

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:

the date parts related in a Tableau date data blend
These fields are related between the data sources

You will know when the joins are correct and active. Tableau puts an orange chain links symbol to signify there’s an active relationship.

Joined fields in a Tableau data blend with the orange chain showing they are connected

16 thoughts on “Tableau Data Blending With Dates”

  1. Thank you for the perfect solution. I unfortunately spent more than an hour on this frustrating task. Once I read your post, it took me only moments to fix my workbook. Andrew, you’re awesome!

    Reply
  2. Thank you, thank you. I spent hours on this and ended up faking it by floating two sheets next to each other on a dashboard in Tableau 8. Your solution worked great to get both data sources in the same worksheet. All I was trying to do was add some monthly numbers to a data set that was at the “day” level and roll it up to monthly. The complication was that I was also using week numbers.

    Reply
  3. I don’t really like joining on dates. I will typically create an int on both sides by using year(invoicedate)*10000+month(invoicedate)*100+day(invoicedate) so that the format is yyyymmdd

    Reply
    • Great idea Niel, converting to INT should definitely make blending on dates less troublesome, thanks for sharing.

      Reply
  4. I find it very useful!
    However, I wander why don’t you make an DateIndex with INT([date]) ?
    in this case you make 1 calculation and join on an number value and it is actually a date.

    I am using Tableau from version 8.2, perhaps was this not possible in the past ..?

    Reply
  5. It seems to be more of an issue with date formatting than anything else (some have times, some have Month, Day, or year in a different order). In my case, one column had the exact time the record was created and the other was set to midnight.

    I solved it 2 ways (I was curious if the calc field would work so tried it)… creating a calculated field in each data set with the formula of Date(DateField), and then creating the relationship on those. Or if you’re lucky enough that they are in a database you can use SQL (or a stored proc, or a view) and add a column to both queries that forces the dates into the same format.

    Reply
  6. HI, I have a problem blend with BETWEEN dates, for example In my primary DS I have Date any in my secondary DS I have Effective Date and Terminate Date and I need to blend Date BETWEEN Effective Date and Terminate Date

    Reply
  7. I am a new Tableau user and have this same issue with dates. In the instructions it says to Open up the date field and there is a screen shot of add/edit field mapping. How and where do you open the date field, and where is the add/edit field mapping?

    Reply
    • It’s Edit Blend Relationships under the Data menu. To Add/Edit Field Mapping click Add when in the Edit Blend Relationships box. Note “Custom” has to be selected to able to select Add and bring up the field mapping box.

      Reply

Leave a Comment