1 Reply Latest reply on Aug 15, 2013 2:05 PM by Mike Swinn

    Sorting one Dimension by Rank within Another

    Mike Swinn

      I am trying to create a chart that demonstrates the spread of peoples activity across multiple categories. Specifically I'm trying to create a stacked bar chart where each person is on a different row and within that row the bars break down each category, with the bars colours not to represent the category itself but the relative importance (ranking) of that person's primary, secondary and tertiary categories (etc.). I am not interested at all in what those categories are, just how much spend value each person 'spends' on their primary, secondary and tertiary categories. I want to see how many people spend in predominantly just the one category (whichever that may be), how many spend mainly across two categories and so on.

       

      See attached example.

       

      The logic behind this is simply:

       

      1) Sum values per category per person

      2) Rank categories per person

      3) Plot stacked bars with the length of the bar being the sum and value, and the colour being the rank relative to that person

      4) Order rankings so that the primary category (rank 1) is at the left, secondary category (rank 2) is next to the right, and so on.

       

      I can manage all of this up to step 4 with simple table calcs, but I cannot figure out any way of getting the sort I want. Sorting by category is not an option as the global totals will be calculated and ranked accordingly, so a category which is of low importance to somebody but highest importance overall will be sorted to the top.

       

      However, I need to use category in the detail shelf or the table calculation will not be possible. It appears that by introducing category here Tableau insists on a standard sort order for category across all people, which means while the colours and bar lengths are correct, the sort order is effectively random for what I'm trying to do. Specifically I would want the red bars to always been at the left, followed by the orange and then yellow.

       

      I am hoping there is a solution involving modifications to the table calcs or sorting on some new calculated field. Any ideas?

        • 1. Re: Sorting one Dimension by Rank within Another
          Mike Swinn

          Finally solved this by creating a field which combines (concatenates) the Person and Category dimensions such that each cell has a fully unique attribute which to sort on within the entire dataset. I then replaced category (which I do not care about on its own) with this dimension in the detail shelf, set the table calculation to address this field, and sorted on this field in the detail shelf also.

           

          Method is similar to this, although I am still able to use a simple index() function. If the fields were not of identical length it would be wise to add padding between the two main dimensions when concatenating, as described in that thread.