By nested sorting do you mean that you are using a table calculation like INDEX() or FIRST() or RANK() to generate the sort?
Actually I am using a sorting with combined fields, as I have several tables in the same sheet. /Nils
I do it w/ a bit of a work around, rather than a programatic solution. I usually just name "other" or "none of the above" as "z Other" or "z None of Above."
That way when sorted alphabetically they're always at the bottom, and I don't have to worry about remembering or documenting a table calc that I create just to sort a list of words.
If I can't control the raw data, I'll do a formula in Tableau along the lines of
IF [ListOfText] == "Other" THEN "z " + [ListOfText]
I set up a quick example in Superstore, using Region as the top-level dimension and Department as the bottom-level with "Furniture" being a stand-in for your "other". Then I created a calculated field Sales for Sort with the following formula:
IF [Department] == 'Furniture' THEN -1000000000000 ELSE [Sales] END
This just makes the sales for Furniture/Office a gigantically negative number. Here's a view that has the combined field sorted on Sales, so we see the nesting working for Sales but Furniture/Other is not at the bottom:
Now I've changed the combined field to sort on the new measure, and Furniture/Other is now at the bottom of every Region:
So the solution Bruce proposed is taking advantage of Tableau's built-in alphanumeric sort of the dimension values. The solution I'm proposing is using Tableau's ability to sort a dimension by a measure and setting that measures's values to be ones that sort in the desired order.
I've attached a packaged v9.0 workbook.
nested sort for other.twbx 1.0 MB
@Bruce: Thank you for that - a trick that i will for sure keep in mind!
Jonathan,thank you very much for that elaborate description! It worked perfectly.
However this is only the case when I'm dealing with single choice questions. They have: Question, label and value - in the rows.
The question is for example Q1, the label is for example - What gender are you? And the value then male or female.
Im trying to do the same with a multiple choice answer, but i cant get that to work. The problem is that the value is now checked/unchecked - so i have to add an extra dimension to get the real value - and not just checked.
The data then looks like this:
Question ( Q1), Label(For example, Which food do you like), Extra label (for example Pizza), Value(Checked/Unchecked)
Do you have any suggestions on how to do the same sorting with the calculated field?