1 Reply Latest reply on Oct 8, 2012 7:30 PM by Jonathan Drummey

    calculate fields and dates with joining data sources

    Anna Chan

      Hi

       

      I have two data sources with the same field Movie Name all of which have movies that release the same day, that data source is the third data source. Is it possible to have all three of these data sources linked, and then create a calculated field which subtracts the release date from the date on the other two sources? any help would be greatly appreciated.

       

      Thanks,

      Anna

        • 1. Re: calculate fields and dates with joining data sources
          Jonathan Drummey

          Hi Anna,

           

          Based on what you'd stated, you wanted to link the data sources. Looking at the workbook you'd posted where the user can choose between different metrics, it really seemed like one data source would be best to avoid the problem that can happen in blends where if you don't have all the level of detail necessary in the primary data source (like having all the dates) then you lose data.

           

          So, I used Custom SQL to set up a UNION query that merges queries that pulls from the Visits and Mentions data worksheets, and each subquery joins on the Release Date worksheet to get that info. That creates a unified data source with Visits and Mentions measures, and creating the Days from Release Date dimension is a simple subtraction. Note that Movie 2 has a release date that is in December 2012, while all the data for it is back in December and January 2011. Also, the Mentions and/or All Traffic worksheets have a lot of Null records in them, deleting those extra blank rows in Excel before they get to Tableau would make the workbook run faster.

           

          Jonathan