2 Replies Latest reply on Jun 13, 2018 4:47 AM by Aditya Singh

    Blend data using date range

    Christian Go

      I need to blend with the second data source having from and to dates:

       

      Main source:

        

      CustomerTransaction DateMetric
      Tableau1/2/201787%
      Tableau1/5/201724%
      Tableau3/14/201777%
      Tableau3/2/201725%
      Tableau2/2/2017100%

       

       

      Blended Source:

        

      CustomerFrom DateTo DateTarget
      Tableau1/1/20171/31/201795%
      Tableau2/1/20172/28/201790%
      Tableau3/1/20173/31/201780%

       

       

       

       

      Is this possible with tableau? basically the transaction date should be in range of the from dates and to dates.

       

       

       

       

      The output should be:

         

      CustomerTransaction DateMetricTarget
      Tableau1/2/201787%95%
      Tableau1/5/201724%95%
      Tableau3/14/201777%80%
      Tableau3/2/201725%80%
      Tableau2/2/2017100%90%
        • 1. Re: Blend data using date range
          Jim Dehner

          Good morning

          Have you looked into data source filters capability in Tableau

          see the link

           

          Filter Data from Your Data Source

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Blend data using date range
            Aditya Singh

            Hi Christian

             

            Yes, you can definitely achieve this in Tableau, please follow below steps:

             

            1. Create a join on Table 1 with Table 2, matching Customer with full outer join. This will give you below output:

                  

            CustomerTransaction DateMetricFromDateToDateMetric2
            Tableau1/2/201787%1/1/20171/31/201795.00%
            Tableau1/2/201787%2/1/20172/28/201790.00%
            Tableau1/2/201787%3/1/20173/31/201780.00%
            Tableau1/5/201724%1/1/20171/31/201795.00%
            Tableau1/5/201724%2/1/20172/28/201790.00%
            Tableau1/5/201724%3/1/20173/31/201780.00%
            Tableau3/14/201777%1/1/20171/31/201795.00%
            Tableau3/14/201777%2/1/20172/28/201790.00%
            Tableau3/14/201777%3/1/20173/31/201780.00%
            Tableau3/2/201725%1/1/20171/31/201795.00%
            Tableau3/2/201725%2/1/20172/28/201790.00%
            Tableau3/2/201725%3/1/20173/31/201780.00%
            Tableau2/2/2017100%1/1/20171/31/201795.00%
            Tableau2/2/2017100%2/1/20172/28/201790.00%
            Tableau2/2/2017100%3/1/20173/31/201780.00%

             

            2. Now, create a calculated field as Flag using expression, if [Transaction Date]>=[FromDate] and [Transaction Date]<=[ToDate] then True else False end

             

            3. Create another calculated filed as Target using expression,  if [Transaction Date]>=[FromDate] and [Transaction Date]<=[ToDate] then [Metric2] else [Metric] end

             

            4. Now use the Customer, Transaction date, Metric and Target columns on row/column shelves and filter out false values from Flag.

             

            5. Final Result:

               

            CustomerTransaction DateMetricTarget
            Tableau1/2/201787%95%
            Tableau1/5/201724%95%
            Tableau3/14/201777%80%
            Tableau3/2/201725%80%
            Tableau2/2/2017100%90%