7 Replies Latest reply on Jan 17, 2014 8:55 AM by Robert Morton

    How to blend data on two tables based on WEEK()

    logan.rott.0

      I have two data sources blended on dates on a weekly basis, however I want to sort based on calculated measures and therefore need to put my data into the same data source. My issue is that doing a table join I can only seem to join where date1 = date2 when I want WEEK(date1) = WEEK(date2).

       

      Is there any way to do this? I know I can modify the underlying data but I'd rather minimize pre-processing if Tableau can actually do this.

       

      Packaged workbook is attached showing the way I do it currently with data sources blended on WEEK().

        • 1. Re: How to blend data on two tables based on WEEK()
          Matt Lutton

          Won't joining on the date fields, then using Week(Date) in your view result in the same?  I'm not sure the level of detail for the join on a date has an impact.  If it does, you'd need to include WEEK(Date) in your data source in order to be able to join on it--we cannot create a calculated field in Tableau and use that for a join.  However, we can use calculated fields to create a data blend.

          1 of 1 people found this helpful
          • 2. Re: How to blend data on two tables based on WEEK()
            Robert Morton

            Hi Logan,

            Matthew has already provided good guidance, but I wanted to point out another technique you may find useful for data blending. If you wish to blend on WEEK(Date) but cannot have that field in your visualization level of detail, consider creating a Custom Date field in both of your data sources: right-click on your existing date field and create a custom date, and select date truncation at the level of Week. Then you can use that custom date field as a linking field between the two data sources without requiring its presence directly in the visualization or level of detail.

            I hope this helps,

            Robert

            • 3. Re: How to blend data on two tables based on WEEK()
              logan.rott.0

              Thanks Robert, Custom Date worked perfectly. I figured there was some helpful function I just had never used before.

               

              Matt, I don't think I'm able to follow exactly what your process is but I get the idea. I'll probably need to work it a bit more since it's potentially a more elegant solution than creating another dimension. Thanks,

              • 4. Re: How to blend data on two tables based on WEEK()
                logan.rott.0

                Follow-up question because now I'm lost. The first time I created the custom date I was able to link it to the date field on my second table, however I moved to my second workbook and have no idea how I pulled up the option to link the two fields together.

                 

                Where is the option to link the custom date with the date on the second table?

                • 5. Re: How to blend data on two tables based on WEEK()
                  Robert Morton

                  Hi Logan,

                  You will need to create the same custom date field in both data sources. If the two fields have exactly the same name, you will see a gray chain-link icon next to the field in the Data window for your secondary data source. Click that link icon to turn it orange and it becomes one of your linking fields for data blending.

                  -Robert

                  • 6. Re: How to blend data on two tables based on WEEK()
                    logan.rott.0

                    Sorry, I don't think I'm describing the situation right. My goal is to get rid of the second data source completely so I can't rely on data blending.

                     

                    Within one data source I can join where date1 = date2 but that ends up excluding any situations where they don't match, and I want them to match if they are within the same week. I think this is just a limitation of joining vs. blending since I don't have a calendar table I could reference with the SQL?

                    • 7. Re: How to blend data on two tables based on WEEK()
                      Robert Morton

                      Hi Logan,

                       

                      Thanks for clarifying. Matthew referred to this challenge in his original comment where he explained that Tableau does not currently support creating a multi-table join on a condition that involves a calculation. You can only express joins directly between fields in each table.

                       

                      An alternative is to write a Custom SQL query that performs the join based on truncating the date to week boundaries, and if you're curious about how to write such syntax (without involving a calendar table) you can simply look at the Tableau log files to see the types of queries Tableau performs to truncate a date to week.

                       

                      Another possibility is to create a database VIEW which selects all fields from a single table, and computes an additional field for the date truncated to week using the same SQL expression that Tableau generates; create such a VIEW for each of your tables, and then you can create multi-table join connection in Tableau that joins the two views (which appear as normal tables in the dialog) along the truncated date field that they have in common. Keep in mind that you may experience a many-to-many cross product of your data that produces more rows than existed in either of the original tables. To avoid this you may have to aggregate all of your measures to the granularity of the week-truncated date, otherwise you may accidentally double-count your measures. In contrast, this is one of the strengths of using Tableau's data blending since it will only perform the blending "join" on the post-aggregated data from each table, avoiding the risk of double-counting.

                       

                      Does this make sense?

                      Robert