7 Replies Latest reply on Jun 12, 2013 9:19 AM by Jonathan Drummey

    Filtering Across Multiple Data Sources

    Jake Utzig

      I have three data sources, all of which can be linked/blended by a common identifier - CustomerId.

       

      The first two data sources, Sales and Visits, are used to create two visualizations: 1) Sales by Category 2) Visits by Day.  The third data source has additional information about each CustomerId (FirstName, LastName, Age).

       

      I am trying to combine both visualizations on one dashboard, and then create a customer filter that applies to both.  Additionally, I am trying to make it such that the customer filter is not the ID, but instead it is one of the dimensions from the third data source, such as Age.

       

      I have attached a sample workbook that lays out this problem.  Any help would be greatly appreciated.

       

      Thanks.

        • 1. Re: Filtering Across Multiple Data Sources
          Shawn Wallwork

          Jake, you'd be much better off doing a join, rather than a blend. In the attached I used Tableau's Multiple Tables option to build a join of your three worksheets. This way you can use filters across all three worksheets since there's only a single connection.

           

          --Shawn

          1 of 1 people found this helpful
          • 2. Re: Filtering Across Multiple Data Sources
            Jake Utzig

            Thanks for the quick response, Shawn.  Your solution works perfectly when all three worksheets are coming from a single connection.  Unfortunately, the sample problem that I provided is somewhat different than the data that I am working with.  The three worksheets that I am using are 3 separate custom SQL statements.  The easy fix here would be to dump the output from the queries into tables, and then link the three tables under one connection.  However, I know there are times when this will not be applicable.

             

            Going back to the sample problem, how would your solution change if we assumed that the three worksheets were each linked to a separate excel file, which would eliminate the ability to access all three through a single connection?

            • 3. Re: Re: Filtering Across Multiple Data Sources
              Shawn Wallwork

              First I can't believe I posted a twb! Shame on me. Here's the twbx. I'll get back to you on the 3 connect. Although I must say if you're doing custom SQL connections, I'd think you'd be able to join the connection in whatever way you need it. But I'll take a look.

               

              --Shawn

              • 4. Re: Re: Filtering Across Multiple Data Sources
                Shawn Wallwork

                Nope got nothing for you. You end up needing two filters if you use the Age as a secondary data source. And you end up with * in the other sources if you use the Age as the primary data source. You are simply hitting up against the limits on Date Blending. It looks to be multiple tables or custom SQL, as far as I can tell.

                 

                --Shawn

                • 5. Re: Filtering Across Multiple Data Sources
                  Jake Utzig

                  I will work to convert the custom sql statements into views that are stored on the same SQL database and then create a single connection with multiple tables.


                  Thanks for your help.

                  • 6. Re: Filtering Across Multiple Data Sources
                    Jake Utzig

                    The problem that I am now running into is that using multiple tables creates duplicate records.  For example: CustomerId 5 has 7 sales and 3 visits.  By using the multiple tables as the connection, I now have 21 records.  Because of this, the total sales for this customer is no longer the sum of the 7 sales ($316.47), but is instead the sum of the 21 records ($949.41).

                     

                    The duplicates can be managed (somewhat) with the use of calculated fields, but that even gets difficult when I want to look at totals versus averages.  The bigger problem, however, is when this method is applied with actual data, where the sales table can consist of 50,000 rows, and same with visits. The duplicates created result in a data set much too large to be handled.

                     

                    Any ideas?

                    • 7. Re: Re: Filtering Across Multiple Data Sources
                      Jonathan Drummey

                      There is a Tableau solution that will work via a Filter Action, I set this up in the attached. Filter Actions don't have the bells and whistles of a quick filter, but they can be used across multiple data sources. The trick is to include the dimension to be filtered (Customer ID) in the worksheet that is the filter, it doesn't have to be in the filtered worksheets.

                       

                      Jonathan