10 Replies Latest reply on Jun 7, 2012 7:07 AM by steve.davies

    Aggregation with multiple data sources

      Hi all,

      I have two data sources: the first contains sales by product code, and the second lists a product hierarchy where product codes are grouped together into categories. There are a lot less categories than product codes.

      When I link the data sets on the product code it all works fine. I can display aggregate sales by product code, with the category listed alongside.

      But what I really want to do is just show aggregate sales by category only. However when I remove the product code from the view it stops working, and only displays a star for the category.

      Is this type of thing possible to achieve?

      Many thanks in advance,

        • 1. Re: Aggregation with multiple data sources
          Alex Kerin

          Try putting product code on the level of detail shelf.

          1 of 1 people found this helpful
          • 2. Re: Aggregation with multiple data sources
            Robert Morton

            Hi Ian,

            It sounds like you want the visualization to be at a coarser granularity than the linking field. This is not currently possible, but it's a common request. I cannot comment on a timeframe or roadmap for addressing this need.

            -Robert

            1 of 1 people found this helpful
            • 3. Re: Aggregation with multiple data sources

              Many thanks for your replies.

              Ian

              • 4. Re: Aggregation with multiple data sources
                Jonathan Drummey

                Hi Ian,

                 

                There are some workarounds, I've attached a workbook with two and another possibility.

                 

                - Set up the Product Category data source as the primary data source, with Product Code on the Level of Detail, then use a table calculation in the primary or secondary to return one result per Product Category. Here's the table calc I used in the Product Category (primary) data source:

                 

                IF FIRST()==0 THEN

                    WINDOW_SUM(SUM([product code#txt (product code.txt)].[Sales]), 0, IIF(FIRST()==0,LAST(),0))

                END

                 

                The IF and IIF statements are set up to only return one result and use the technique developed by Richard Leeke here: http://www.clearlyandsimply.com/clearly_and_simply/2011/01/another-look-at-site-catchment-analysis-with-tableau-6-part-3.html. The key part is the WINDOW_SUM(), which pulls the Sales from the secondary. With the Compute Using set to Product Code, this gets the job done.

                 

                - With the Product Code data source as the primary, use Tableau's Primary Groups feature to "push" the Product Category from the secondary into the primary. In some ways, this is the simplest because you get to use SUM([Sales]) and don't have to bother with table calculations, but requires ongoing work to update the groups anytime the Product Code/Product Category mapping changes.

                 

                - The possibility comes from not knowing what your data sources are. Depending on where the data is coming from, you might be able to use the multiple tables option or Custom SQL, and/or make the Product Category data available to the Product Code data via an ODBC or other data connection, and then use the Multiple Tables or Custom SQL to create one data source in Tableau.

                 

                Cheers,

                 

                Jonathan

                • 5. Re: Aggregation with multiple data sources

                  Jonathan,

                  Your (& Richard Leeke's) solution works perfectly, many thanks for sharing!

                  The custom calculation is a little complex, so it's not absolutely 100% "out of the box" functionality, but I anyway I just had to change the field name in the formula, which is easy.

                  The only note I would add is that I had to change the "Compute Using" property in two places to avoid overprinting: once in the calculation properties under "Default Table Calculation", and once again in the Marks card via its down arrow.

                  Multiple tables option was not available because the two data sources come from different places. Also I preferred not to use primary groups due to the likelihood of ongoing maintenance.

                  Thanks again,

                  Ian

                  • 6. Re: Aggregation with multiple data sources

                    Jonathan,

                     

                    You mention that depending on the data sources, you may be able to use an ODBC to create a single data source. If in Ian's example, the sales data are in a SQL server and the product categories are in an Excel worksheet, could these sources be combined into a single data source in Tableau?

                     

                    In addition, in the WINDOW_SUM example you gave, how would you add an additional dimension, such as the year of sale?

                     

                    Thanks,
                    Steve

                    • 7. Re: Aggregation with multiple data sources
                      Jonathan Drummey

                      @Ian: 

                       

                      You're welcome!

                       

                      Technically, you only need to set the Compute Using once for a given view. When you set the Default Compute Using, that's what Tableau uses when the calculated field is brought into the view. When you set the Compute Using by clicking on the green or blue pill in the view, that sets it only for that pill in the view (and does not change the Default).  I'm going to guess that you had the pill already in the view, set the Default Compute Using, found out that it didn't actually change the Compute Using for the pill, and then changed the Compute Using for the pill. Does that make sense?

                       

                      @Steve:

                       

                      You can go in either direction. You can add an Excel worksheet as a data source to SQL Server or vice versa, if you do a web search you can get instructions for both ways.

                       

                      As for adding an additional dimension, I'm not sure what you are describing, can you give more detail? Often a packaged workbook (.twbx) with a mockup of what you are trying to get to is the easiest way to describe what you are going for.

                      • 8. Re: Aggregation with multiple data sources

                        Jonathan,

                         

                        Thanks for your response. To demonstrate what I meant by an additional dimension, I edited your example, adding a field for the year of the sales data. I'd like to aggregate according to the categorization data source as well as the year in the sales data source. I'm new to Tableau, but it seems that I need some sort of CELL_SUM rather than WINDOW_SUM? I'd appreciate any thoughts you have.

                         

                        Thanks,
                        Steve

                        • 9. Re: Aggregation with multiple data sources
                          Jonathan Drummey

                          Hi Steve,

                           

                          You can't use data blending to have a product category as the primary data source and the product code w/year as the secondary and break down a view by year. The blend is a left join from the primary to the secondary, so any fields in the secondary that don't exist in the primary (such as the Year field in this example) must be aggregated (only the fields that are specified as part of the blend in the secondary are non-aggregated). That's why you get the * in the view when you brought Year into the view - Tableau is telling you that the aggregation of Year is returning multiple values.

                           

                          Therefore, you'd need to use primary groups or do the work to link the product category to codes in the underlying data source.

                           

                          Does that make sense?

                           

                          Jonathan

                          • 10. Re: Aggregation with multiple data sources

                            Yes, that makes sense. I was guessing that would be a limitation but wanted to make sure I wasn't missing anything. Thanks for the in-depth response!

                             

                            Steve