VizWiz

Launch, grow, and unlock your career in data

March 17, 2015

Tableau Tip Tuesday: Two Techniques for Nested Sorting

25 comments
Early on in my 2-day Tableau courses, I teach about sorting in Tableau. You would think that teaching about sorting would be a pretty boring topic, but it’s easy to get the students engaged when you talked about nested sorting. Another way to think about nested sorting is to consider it sorting by more than one dimension.

In this week’s tip, I review two techniques for accomplishing nested sorting:

  1. Using combined fields
  2. Using the rank function, which has the additional benefit of allowing top N filtering

Download the workbook used to create this video here.

25 comments :

  1. Hi Andy. Very helpful, as always. You may want to check your size, as this viz does not fit in the blogger window; it's usable but you have to scroll left and right.

    ReplyDelete
  2. Thanks Jon, I'll check it. It's the same size as every other week. Are those too big as well?

    It also could be the size of your screen. It fits in my screen. The blog has responsive design based on your browser whereas Tableau does not.

    ReplyDelete
  3. I'm seeing the same thing. This is the first time I've encountered it.

    ReplyDelete
    Replies
    1. Peter/Jon, it should be fixed now. The problem was that I published worksheets and not dashboards, so Tableau made them really wide for some reason. I've moved them to fixed size dashboards now. Thanks for letting me know.

      Delete
  4. Hi Andy, thank you for the Viz. It was really helpful. But if the requirement was to compare sales for all Regions/Sub-Categories together, you can create a calculated field with index() and drag it on the Rows shelf as the first column (Discrete). Sort the Sales in Descending Order and Hide the index value. But this doesn't help in the TOP N values filter. Let me know if this was helpful and if you can help me use this method to get the TOP N values. Thanks !

    ReplyDelete
    Replies
    1. Ajay, before the RANK function existed, the only way to do this ranked sorting was via the INDEX function as you've done.

      Delete
    2. Andy, how can I use this method for filtering the TOP N values? Would a combination of Parameter and Calculated Fields be the only way?

      Delete
    3. Ajay, the technique I outlined using the rank function will work.

      Delete
  5. Nice video! I just wrote a post on the same topic using Rank_Unique(). My only concern of using Rank() is that it may produce non-distinct rankings. This may not be a concern for sales figures which are hardly duplicates.
    http://vizdiff.blogspot.com/2015/03/nested-sorting-and-top-n-per-category.html

    ReplyDelete
  6. BTW, your workbook needs Tableau 9 to open.

    ReplyDelete
    Replies
    1. Yes, we (mere mortals still on 8.3) can't open it.

      Delete
  7. Andy, as always your site comes up when looking for tips on "do X in Tableau" I came across another "dual" sort situation. I want to sort by "Sales" first, and then by "Cost" second. So in the event that two groups tie (say if they have zero sales) then I want to sort the second bars by cost.

    I had to do it in SQL because I couldn't sort on a Table calculation, but I made a calculated field called "dualsort" = sales_rank*100 + cost_rank and then sorted on that and it worked perfectly.

    I suppose we could call this "pivot sorting"? Two column sorting?

    ReplyDelete
  8. Brilliant and very helpful, thankyou

    ReplyDelete
  9. Whenever I add a color mark to my rank sorted graphic it messes up my sorting... Are you familiar with this?

    ReplyDelete
    Replies
    1. Yes, that's because you changed the level of detail to be more granular. You likely need to include this in the Addressing section of the advanced table calc window.

      Delete
  10. Hi Andy,

    Thanks for this tutorial! Although it was pretty useful for one my projects, I get stucked once I want to include a third Dimension in the rows. I tried to replicate the process depicted in both of your examples but it seems that Tableau is not sorting the way I want (i.e. first per region, then per subcategory, and finally per another new subcategory). I was wondering if you could help me out with this issue. Thank you!

    Fede

    ReplyDelete
    Replies
    1. Create a calculated field that concatenates the three fields together. Then sort on that field.

      Delete
  11. This is definitely helpful. I am trying to do the same thing, but also add an "other" category where everything after the top N values gets put. Is there a way to do this?

    ReplyDelete
    Replies
    1. Ryan, I've added a tab to the viz above named Top N & Other. If you download the workbook, you'll see how I made it work with table calcs. I suspect there's an easier way with LOD calcs, but I didn't spend time trying to figure that out. I knew I could get it to work with table calcs.

      Delete
  12. Hi Andy!

    I've a question wish you can help me out. I've successfully ranked the bar chart and got top 10 in one worksheet. How can I apply this Top 10 filter in another worksheet directly? I'm going to create a map and only show the top 10 data in the map.

    ReplyDelete
    Replies
    1. Hi Eva. If you're using a RANK, which is a table calculation, you can't make it work across worksheets as table calcs only work on the current view. You could write a FIXED LOD instead to apply across the worksheets.

      Delete
  13. Hey Andy,

    Is it possible to hide/remove the rank column on the graph?

    ReplyDelete