Data Viz Done Right

October 24, 2014

Tableau Tip: Blending data sets that have different max dates

3 comments
Fun little problem at work today. Imagine you have four data sources that all have a Date field in common, but they all land in your database at different times. You want a view of the data for the latest date that they all have in common.

Here's a sample of data the represents the situation:

            

Inside Tableau, these are four different data sources.  I blend them all together onto a single worksheet like this:


What I desire is to only show the data for the latest date that all four data sources have in common, 10/17/2014 in this example. While there might be a more efficient way to perform these calculations, I want to share the approach that I took to solve the problem.

First, I created a calculated field in each data source to get the max date. Note that I've set the table calculation to calculate along Date.


I then added each of these calculations to the view to make sure I was getting the results I wanted.


Sweet! This is exactly what I was hoping for. Next I need to determine which one of these four dates is the minimum and then return the date from that data source. This is the calc I created and it's kinda messy, but it works. Note this I set the calc to compute along Date.


This calc returns 10/17/2014, which is exactly what I was expecting. One more step. I need to create a calculated field to use as a filter in my view so that I only see one row.


I add this to the Filters shelf, set the Compute using to Date and choose True. That's it!


If anyone knows a more efficient way to do this, I'm all ears! Download the workbook here.

3 comments :

  1. Link to workbook: https://www.dropbox.com/s/de2ku3bkxvoqkp4/FourBlendsSample-MMMedit.twbx?dl=0

    ReplyDelete
  2. If I'm understanding the issue correctly, the key is that "NULL if any value in calc is NULL":

    MAX([Date]) =
    WINDOW_MAX(DATEADD("second",
    DATEPART("second", ATTR([Date]))+
    DATEPART("second", ATTR([Engagement].[Date]))+
    DATEPART("second", ATTR([Likes].[Date]))+
    DATEPART("second", ATTR([Views].[Date]))
    , MAX([Date])))

    ReplyDelete
    Replies
    1. Thanks Matthew! I knew there was likely a more efficient way. Part of what I wanted to show was how I thought through the problem.

      I tend to go back and clean up and simplify afterwards.

      Delete