4 Replies Latest reply on Jul 12, 2017 8:00 AM by Matthew Araujo

    Nested top N without displaying the dimension being 'nested into' in the view

    Matthew Araujo

      Hi All,

       

      I am having trouble with something that sounds straight forward in my head but is proving difficult to implement in Tableau!

       

      I want to do a nested top N, but I want the field I am 'nesting into' to not be on the shelf or visible in the view.

       

      Using the superstore data as an example, I would want to see the Top N sub-categories by sales for each region. But crucially, I don't want sub-category to be displayed in the view.

      • I have tried using sets - but these calculate across the entire dataset. I want the top N to recalculate for each region.
      • I have tried using the ranking and index functionality. But these seem to fall over when I remove the field I am ranking by from the view
      • I have tried using combined fields as well as manually concatenated fields, but I cannot get the top N to recalculate at each partition with either method.

       

      So you have a rough idea of what I am trying to do in my actual job - I work with health data and am trying to compare the performance of our top 20 consultants for each of our 30 odd hospitals. We envisage this being displayed as a list of our hospitals, with total revenue generated by the top 20 consultants at each site.

       

      If anybody could help me out with this it would be greatly appreciated! I feel like I'm missing something obvious....

        • 1. Re: Nested top N without displaying the dimension being 'nested into' in the view
          Michel Caissie

          Matthew,

           

          You can accomplish this using the Rank() function  and putting the sub-Category on the detail shelf instead of the column.

          See in the attached on sheet 3.

           

          From a duplicate of the sheet Using Rank;

              -Drag the sub-Category on the Detail shelf

              -Remove the Rank

              -Set the computing of the filter ,  Compute using Sub-Category, keep True values

              -As you see you get one mark per category on the row. You can hide the borders by going in Color, and set the same color in border

              - If you want to show the Region Sales you can compute using

                    WINDOW_SUM( if [Rank] <= [Top N] then ( SUM([Sales]) ) end )

                        You need to reapply the Top N filtering localy in the calculation here, otherwise because you have a table calc in the Filters and the order of operation of

                         Tableau, the Window_Sum would be computed before the Top N filtering

              -If you want to Sort desc,  you can put a discrete value of the Negative value of the same Window_Sum at the left of the Rows and hide the values (unselect Show Header)

           

          note: you could also put the Window_Sum on the label shelf, but to display the value on a single Region  , sub-Category Mark  , you would need to compute it using

               if Rank = 1 then WINDOW_SUM( if [Rank] <= [Top N] then ( SUM([Sales]) ) end ) end

          in order to display it only on the first mark.But then you will have alignment problems because you cannot control the sort order of the Category. And you could have overlapping problems if the

          first category displayed is to small.

           

          Michel

          2 of 2 people found this helpful
          • 2. Re: Nested top N without displaying the dimension being 'nested into' in the view
            Matthew Araujo

            Thanks Michel,

             

            This is quite an elegant work-around that works well in situations when using a bar chart. I don't suppose you have another work-around up your sleeve for instances where a simple table of numbers would be used as the visualisation instead of a bar chart?

             

            If I've understood correctly, the above won't work when using a table of numbers as having the sub-category on the LOD shelf would break those numbers up by sub-category on each row?

             

            Sorry to be a pain ^_^

             

            Thanks again for your help!

            • 3. Re: Nested top N without displaying the dimension being 'nested into' in the view
              Michel Caissie

              Matthew,

               

              Check on sheet 5.

               

              From a duplicate of Sheet3, just remove the Sales on the Columns and  set the Marks to Line with Color opacity at 0%.

              • 4. Re: Nested top N without displaying the dimension being 'nested into' in the view
                Matthew Araujo

                Ah! that makes sense.

                 

                Thanks very much for your help ^_^