6 Replies Latest reply on Nov 27, 2012 5:25 PM by Michael Mixon

    Top N within Top M

    Nkosi Smart

      Hi guys,

       

      I've run into a wall here with this filter. I'm trying to get a sales report where I want to see the top N items and within those items I want to see the top M vendors selling those items. Can anyone help?

        • 1. Re: Top N within Top M
          Joe Mako

          This is one of my complaints with Tableau, their view of Top N in their filter dialog, and soon to be Set dialog, is almost never useful, and only useful in the most basic of situations.

           

          To get the result you are looking for, you would need to use nested table calculations, the attached is an example. Top States and Top Sub-Categories.

           

          If you want to change the arrangement of pills, or view, you will want to use the "Show Me" to first lock in the table calc configurations as it is based on the layout.

          1 of 1 people found this helpful
          • 2. Re: Top N within Top M
            Nkosi Smart

            Thanks Joe! This is helpful but I think I should've explained a little further. I'm using analysis cubes, would the process be the same?

            • 3. Re: Top N within Top M
              Joe Mako

              I do not have a cube data source, so I cannot say for sure, but I would expect this route to work the same for any data source because the computation is happening in the data interrupter, after query results are returned from the data source. Please let me know if you have any issues after you attempt this.

              • 4. Re: Top N within Top M
                Michael Mixon

                Hi Joe,

                 

                Can you explain why you chose not to use Index for State rank?  I tried doing that (see Sheet 2) and got some weird results.  I suspect this is why you wen the route you did, but I'm curious as to why Tableau can't seem to just rank the States based on State volume in all cases...it does well through rank 5, but then starts calculating the Copiers and Fax volume separate from the other sub-categories when assigning rank.  Seems random, but I'm guessing there's some method behind the apparent madness.

                 

                Your insights are, as always, appreciated.

                 

                -Mike

                • 5. Re: Top N within Top M
                  Joe Mako

                  You get those results because the domain is not being completed. See "Example domain not complete" in the attached for another perspective of the same computation on the "Sheet 2" you added. You can see that both Ohio and Washington have a rank of 6.

                   

                  The formula used has the addressing/partitioning logic built into the formula, and relies upon the relative Table (down) compute using, that the "Show Me" button can lock in.

                   

                  This same issue does not apply to the ranking of Sub-Category, because it is a nested sort. It sorts the concatenation of State and Sub-Category, and then restarts the count for each State. So the domain being or not being complete has no impact.

                  • 6. Re: Top N within Top M
                    Michael Mixon

                    Thanks Joe.  That makes sense now.

                     

                    -Mike