2 Replies Latest reply on Mar 3, 2017 2:31 PM by Kristin Fitzpatrick

    How to rank sort measures within a dimension?

    Kristin Fitzpatrick

      I'm visualizing clinical trials data and want to sort universities doing the trials by 3 measures within a dimension (tbwx attached, Tableau 9.3).

      The dimension is "Status Type," and the measures in the order I want to rank them are:

      1. Enrolled
      2. Screening
      3. Eligible

      My goal is a dynamically sorted chart, with these measures grouped by university, that looks like tab 1 "Goal (but manually sorted)."

      I've created calculated fields for the three measures and am able to sort by "Enrolled" without problem (tab 2 "Sorted by Enrolled Only").


      The problem starts when I try to use all 3 created "rank" calculated fields to sort. (Shin kindly explained how to do this in response to a previous sorting question I had, saved in tab 4 "Old sorting solution".)

      Instead of keeping the "Status Type" measure values grouped by "CC Name," Tableau groups the measure values together and splits up the universities (tab 3 "Ranked Unsuccessfully").


      How to rank sort Enrolled, Screening, and Eligible while keeping them grouped by university?

      Any help much appreciated!

        • 1. Re: How to rank sort measures within a dimension?
          Joe Oppelt

          Here's what's happening to you.  In your "Old Sorting" sheet, you only had MeasureNames and MeasureValues in play.  Essentially one dimension.  Now you have many, and the marks are dictated by those dimensions.


          I messed with your Sheet #2.  Here is the step-by-step things I did.


          I made a calc:  [index].  When we're playing with table calcs, it really helps to understand what Tableau sees as the "next" cell. Index shows that. Then, when playing with the rank calcs, you can see what Tableau is doing with your data.


          One of the problems with sorting by RANK is that when there is no data, Tableau calculates NULL, and pushes all the NULLs to the front.  All of the individual marks (each separate color box) gets an evaluation for the RANK table calc, and only the yellow ones get a rank value.  All the greens and blues don't.  They get NULL and get shoved to the front.

          So I made [Calculation1] that uses a different table setting (restart every [CC Name]) and it assigns the [Eligible Rank] value to all marks within each [CCF Name].  Now, no matter what Status Type a mark is, it gets a usable [Eligible Rank] value, and then [CC Name] "chunks" all act together.

          You can do the same with the other ranks.

          1 of 1 people found this helpful
          • 2. Re: How to rank sort measures within a dimension?
            Kristin Fitzpatrick

            Beautiful, thank you!

            You're right - works great with other ranks as well.

            I thought a Window function might help but wasn't sure how - good to know.

            Appreciate the fast response!