3 Replies Latest reply on Apr 14, 2016 6:09 AM by A S

    Sorting and Top N by Measure names and values

    A S

      I am trying to sort the bars in descending order within each category in F1 (a,b,c, overall) and want to display only the top 3 measure names in terms of measure values within each category e.g. within A, 'q' is first in the order followed by 'd' and 'f'. 's' and 'v' don't appear.

       

      Nested sorting doesn't work and other solutions on the web didn't help.

       

      The file is attached.

        • 1. Re: Sorting and Top N by Measure names and values
          A S

          anybody who can answer the above question?

          • 2. Re: Sorting and Top N by Measure names and values
            Dan Sanchez

            Hello A S!

             

            In order to do this type of nested sorting we'll need to first do some reshaping of the data.  The first step is to use Tableau's Pivot feature on the A, B, C, and Overall columns:

             

            This will give us new fields called [Pivot field names] and [Pivot field values] (these can both be renamed if needed).

             

            Next we'll create a calculated field which I have named [Sorting Field]:

             

            Next we'll build out our viz with SUM(Pivot field values) on Rows and Pivot field names, Sorting Field, and F1 on Columns.  In order for the sorting to work properly, before dragging Sorting Field out onto the viz, first right-click it in the data pane and select Convert to Discrete:

             

            After we have Sorting Field on Columns we'll need to next configure the advanced table calc settings by right-clicking the field and selecting Edit Table Calculation then  making sure the advanced settings match this screenshot:

             

            We'll also need to deselect the Show Header option for Sorting Field to make the viz look a little bit cleaner:

             

            And lastly, we can use the ctrl+click method to drag a copy of Sorting Field from Columns onto the Filters shelf (this lets tableau remember the advanced settings we just configured) and then setting the calc to only show values 1, 2, and 3.

             

            Hope this helps!

             

            Thanks!

            1 of 1 people found this helpful
            • 3. Re: Sorting and Top N by Measure names and values
              A S

              Thanks, Dan! It worked.