5 Replies Latest reply on Sep 29, 2016 1:33 PM by Joe Oppelt

    Top N of a specific level in a dimension, and display only the same top N in another workbook

    Rainer Sabangan

      Hi,

       

      Attached is the packaged workbook.

       

      What I would like to do is to just display the top 3 (no tie) Order Priorities with the highest Express Air transactions (by Product Category and Region) in the 'Proportion of Sales by Ship Mode' view.

      For the example below, I should only see Low, High and Not Specified order priorities in the Proportion of Sales view.

      The Top 3 may be different for when I change the filters.

      I would really appreciate any help.Thanks.

       

      Rainier

        • 1. Re: Top N of a specific level in a dimension, and display only the same top N in another workbook
          Joe Oppelt

          (Note to self:  V8.2 workbook)

           

          See attached.


          You have to re-calc the ranking in the second sheet.

          1 of 1 people found this helpful
          • 2. Re: Top N of a specific level in a dimension, and display only the same top N in another workbook
            Rainer Sabangan

            Joe,

             

            Thanks for the solution you provided. It did exactly as I was looking for.

            I tried using your solution for multiple column dimensions but I cannot figure out the answer.

            I am attaching an updated workbook, with the 2nd sheet having 2 column dimensions.

            Would you be able to come up with a general solution for 3 or more column dimensions?

            Thanks

            • 3. Re: Top N of a specific level in a dimension, and display only the same top N in another workbook
              Joe Oppelt

              You have broken "Express Air" into separate columns by the new dimension.

               

              Given that break-out, how do you want to determine the top-3?  (And ditto when you have a third dimension.)  Would it be the sum of counts for each [Order Priority], no matter how many sub-dimensions it's broken into?

               

              We may need one of the cool new features that started in version 9.0 to do that.  But first help me understand what you really need here so I can give it a shot.

              • 4. Re: Top N of a specific level in a dimension, and display only the same top N in another workbook
                Rainer Sabangan

                Joe,

                 

                Top 3 of the sum of all customer segments with ship mode express air.

                Or the same top 3 order priorities as the 1st sheet.

                In general, regardless of the number of column dimensions on the 2nd sheet, it should display the same top 3 order priorities from the 1st sheet.

                I am currently using v8.2, so i'm hoping your solution will work on our version of tableau.

                And thanks for your help, I really appreciate.

                • 5. Re: Top N of a specific level in a dimension, and display only the same top N in another workbook
                  Joe Oppelt

                  Sort of got something for you...

                   

                  See attached.

                   

                  In the "Sales2" sheet I modified the table calcs to handle the new dimension.


                  The problem you're going to run into is that with each new dimension, the mix of table calcs need to operate differently.  There isn't one setup that will handle changing numbers of dimensions in Tableau V8.x.

                   

                  So, for instance, the SUM(Sales) table calc (the quick table calc for percent of total), is OK with doing PANE(Across) because "Pane" is defined by [Ship Mode].  Therefore the [Sum of Records by SHip Mode] calc can also do PANE(Across).  You are getting a clean RANK for Express Air on this sheet.

                   

                  When you add the third dimension (I added [Container]) then "PANE" means something different.  You can see that Tableau is calculating the SUM(Sales) percent-different calc within the [Customer Segment] Dimension.  (See Sheet 3.)  So I added some stuff to Sheet 4 to re-compute the SUM(Number of Records).  It takes a series of table calcs to add up one dimension in one direction, and the next dimension along another level.  And even with that, within Express Air, we can see that we get different total counts from Segment to Segment because some Containers don't exist from Segment to Segment.  (See the red data values.  That's the total count.)  To address that, I created yet one more table calc to grab the window_max of each Segment.  This pile of table calcs finally result in the value of 39, for instance, for [Order Priority] = "Low".  (Which is the value we needed to fine to do the proper RANKing.)

                   

                  My next step, then was to copy all this to Sheet 5 to grab a final [Just get top 3 Express Air] ranking.  This is where I met new trouble.

                   

                  I made a modified calc [Ranking by overall Express Air] for this sheet.  Because some cells within Express Air don't have data, the rankings get skewed in some columns.  And that's because the calc has to evaluate for "Express Air", and the cells that don't have data therefore don't get evaluated for the ranking function.

                   

                  Now I realize that this is a sample Superstore example we're playing with.  Maybe your data is dense enough that this null data problem won't occur.


                  The reason I pointed to V9.x of tableau is that we have a new function called LOD (level of detail) that streamlines what we could only do with table calcs before 9.0.  And it can eliminate some of the missing-data implications.  Also LOD calcs don't carry the limitations of table calcs.

                   

                  I'm going to leave this example unfinished at this point because the dependency on the data is coming into play, and what we complete here may not be the way you will have to do it in your actual workbook.  (Or what we have to do here may not be necessary in your actual workbook.)  Table Calcs can be tricky creatures.