2 Replies Latest reply on Oct 4, 2018 3:19 PM by Matt Day

    Identifying leading causes across multiple cuts of same data source

    Matt Day

      Hi Tableaurs

       

      I'm trying to do something which I hope is possible in Tableau

       

      I want to be able to calculate the highest and lowest 5 values across combinations of columns, but compare results across the combination types.  It’s the equivalent of wanting to understand in the Sample Superstore dataset which 5 combinations have the highest profit across all of the following:

      All combinations of Segment/State & All combinations of State/Category & All combinations of Segment/Category

      To do any of these in isolation is simple, but to combine the three gets tricky. Using the Sample Superstore dataset the correct answer would be

      1. Consumer Technology +70,798 (Segment, Category)
      2. Consumer Office Suppliers +56,330 (Segment, Category)
      3. Corporate Technology +44,167 (Segment, Category)
      4. New York Technology +42,187 (State, Category)
      5. Corporate Office Supplies +40,227 (Segment, Category)

       

      I understand there is going to be some duplication here, that New York Technology is likely driving the results of Consumer Technology and Corporate Technology – that’s fine.

       

      Is it possible to achieve this by duplicating the data source?  What type of join would I need to use?  I have a very large data set, if I duplicate the data source, does it actually multiply the file size in a packaged workbook?

      Alternatively, is there something I could do which runs the analysis 3 times separately (for Segment/State, State/Category and Segment/Category) and then combines them into one blended output worksheet?

       

      The example workbook has the 3 different combinations giving the result I need, but I need help with the step of combining them into a single worksheet to get the overall top 5 above

       

      Using Desktop version 10.5 to develop, but this will eventually be available to the broader audience on Server

        • 1. Re: Identifying leading causes across multiple cuts of same data source
          Patrick A Van Der Hyde

          Hello Matt,

           

          Is there a reason that a dashboard with 3 worksheets for each of the 3 conditions would not work?   If it has to be in a single view then I think the only solution I can figure out would be to do a union across 3 copies of the data with a common field name that Segment/State field in the one instance where State has to be on the left. 

           

          This sounds like a good opportunity to utilize Tableau Prep to fix your data up as desired before bringing into Tableau.

           

          Patrick 

          • 2. Re: Identifying leading causes across multiple cuts of same data source
            Matt Day

            Hi Patrick

             

            Thanks for the response.  It's one data source, so shouldn't (I don't think!) be a data cleaning issue.

            I'm trying to use one source to find out what the bigger driver is on the business - am I successful because of a certain geography?  Because of a certain product?  Because of a certain retailer which is pushing the product?  While I can run these three analyses separately, and create three separate tables, it's more informative to the user if they can only see the key drivers.  For instance, if the biggest three drivers are 3 different retailers, but there's no real geographical or product influence, showing the best geos or products isn't really very helpful to the user.

             

            So yeah as a workaround the three solutions separately will work but I was hoping for something more elegant.  Perhaps it's out of scope, it's probably a rare problem that people face...