5 Replies Latest reply on Jan 23, 2014 12:24 AM by kettan

    Top N records within a category in a table with several dimensions

    Paul McMurtry

      Hello!  I'm trying to create a table in Tableau to display the top n records within a category.  In addition, the table also needs to display data from several other dimensions.  I'm having problems getting the table to properly display data from other dimensions.

       

      My sample records consist of individual sales opportunities and my categories are the sales teams that own the opportunities.  I need to create a table that will display the top 3 sales opportunities within each sales team.  The top 3 opportunities within each sales team are based on the total value of each opportunity.  The table also needs to include data from several other dimensions.

       

      I was able to incorporate nested sorting based on input from http://kb.tableausoftware.com/articles/knowledgebase/nestedsorting and I was able to determine the top 3 sales opportunities within each sales team using the INDEX() function based on input from http://kb.tableausoftware.com/articles/knowledgebase/finding-top-n-within-category.  Both discusions were very helpful.

       

      The problem I'm running into is getting the table to display other dimensions for the top 3 opportunities within each sales team.  The other dimensions include the opportunity owner's name, close date, and monthly revenue details.  When I add a new dimension to the Rows shelf, the indexing seems to break and Tableau includes all the opportunities in the table.  This is not what I want.

       

      The worksheet named 'Desired Report' in the attached twbx file contains an image of the report I'm trying to create.  The worksheet named 'Top 3 by Team by Opportunity' contains the table that I was able to create that lists the top 3 sales opportunities within each sales team.  The table is correct, but it is missing the other dimensions that I want to display.

       

      Any suggestions to help me create the desired table would be greatly appreciated.  Many thanks in advance for your help.

        • 1. Re: Top N records within a category in a table with several dimensions
          kettan

          This challenge was too difficult for me, but hopefully some expert will show us that it is possible to do this.

           

          That said, I almost reached home. The only hatch is that one has to choose between two bad options:

           

          1. having an ugly empty extra line after each row or
          2. no row totals

           

          I hope some Tableau experts reads this and comes up with a better solution.

           

          See more in attached workbook and screenshot (if it fits your screen).

           

          thread 137402 Top N records within a category in a table with several dimensions.png

          Workbook version:  8.0

          • 2. Re: Re: Top N records within a category in a table with several dimensions
            Jonathan Drummey

            Here you go:

             

            2014-01-22 15_47_15-Tableau - Top 3 Example - Kettan edit.png

             

            This one is a bit tricky because you want to show two levels of detail at once, and part of the extra detail is having a crosstab where Tableau will want to do domain completion if we have dimensions on Rows and Columns. That's why you were getting the extra rows, Johan, you were getting Null values for the Total Opportunity Value in the domain-completed rows.

             

            One thing I try to do in layouts with lots of fields and table calcs is to keep the number of dimensions at a minimum. This reduces the complexity of addressing and partitioning. In this case, the only dimensions we need are Sales Team, Opportunity Name, and the Revenue Date.

             

            For Revenue Date, to avoid domain completion I created an additional field that is DATETRUNC('month',MIN([Revenue Date])). That will be the blue pill on Rows to generate the date columns. All the other fields needed (Owner, Close Date, and Total Opportunity Value) will be aggregated.

             

            The Index calculation has the following Compute Using settings:

             

            2014-01-22 15_48_24-Tableau - Top 3 Example - Kettan edit.png

             

            The addressing is on all of the dimensions, sorted by the Minimum of Total Opportunity Value. When we have multiple dimensions in Addressing, Tableau creates a "set" of these dimensions for the sort. If I sorted on the Sum of TOV, that would end up summing up for every month, which would result in incorrect results. "At the level" on Opportunity Name tells Tableau that instead of incrementing for every new combination of Sales Team, Opportunity Name, and Revenue Date, to only increment the index when there's a new value of Sales Team and Opportunity Name. However, the Restarting Every on Sales Team tells Tableau to completely restart the index when there's a new value of Sales Team.

             

            Note that I didn't use a combined field to do the sort, I use a hidden Index pill. In most views I'd use the combined field, however because of the addtional complexity of the Revenue Date in this view the way I approached the problem it was eaiser to just use the index. There totally are solutions that could use a combined field.

             

            Once I got the index right, then I Ctrl+Dragged a copy of it over to the Filters Shelf, made it continuous, and filtered for the At Most 3.

             

            Jonathan

            • 3. Re: Re: Top N records within a category in a table with several dimensions
              kettan

              Thanks, Jonathan. This is a helpful explanation to understand more about Tableau's inner workings.

              I have printed this thread and will read your answer more carefully later (time to go to sleep in my time zone).

               

              I think Paul gets all he asked for in your solution.

              But it would not have worked if he also needed subtotals, because no aggregation is allowed for discrete measures:

              thread 137402 subtotals of discrete measures are not supported.png

              If there is an issue for the discrete measure, it also disables subtotals for continuous measures 

              It would be nice if it just ignored the totals for the discrete measures and allowed continuous measures to work. Or even better: supported totals for discrete measures.

              • 4. Re: Re: Top N records within a category in a table with several dimensions
                Jonathan Drummey

                You're right, subtotals won't work with what I posted. That doesn't mean that subtotals won't work at all, it just drastically increases the level of difficulty for this particular view.

                 

                I don't understand what you mean by disabling subtotals for continuous measures, Tableau can generate subtotals for continuous measures. For totalling over discrete measures, there are some real questions about the level of detail of the view there, that would be taking the "generate the total over the displayed marks" Idea to an extreme. For example, in the domain completed view that you'd put together, should Tableau be generating a subtotal for each value of Total Opportunity Value?

                 

                Jonathan

                • 5. Re: Re: Re: Top N records within a category in a table with several dimensions
                  kettan

                  I don't understand what you mean by disabling subtotals for continuous measures

                  What I meant is that subtotals seemingly can't be turn on when a discrete measure pill is on a column/row shelf.

                   

                  After playing with your workbook by removing/replacing/modifying various discrete pills, I see my statement is only partly true and thus (partly) false. If instead I had stated that row totals cannot be turned on when any discrete measure pill is on a column/row shelf, it seems it would have been a true(r) statement.

                   

                  This thread and your contribution has helped me getting a better understanding of Tableau totals. Thank you for this and many other patient explanations of the inner workings of Tableau.

                   

                  thread 137402 tableau naming convention - continuous vs discrete measure.png