In Tableau, sorting and filtering are two of the most common features that data analysts usually apply when working with data. In some cases, I would let the user sort by themselves. Therefore, custom sorting in Tableau would be helpful. In this blog, I am going to share how to customize the sort for fields by letting the user input the field for sort by and the order.
In this blog, I use the Sample-Superstore dataset in Tableau. I drag the Sub-Category into Rows and the sum of sales, profit, and average discount into Rows. Make sure all pills are discrete. (Image 1)
There are only 3 steps to do it:
1/ Create parameters
2/ Create a calculated field for sorting by fields
3/ Setting the Sort on the dimension field
Are you ready to make a dynamic sort? If yes, let's get started!
1/ Create Parameters
We need 2 parameters. One parameter is for the Sort By field to let the user choose the field to sort. Another parameter is for Sort Order to let the user choose the order in Ascending or Descending.
First, I created a parameter Sort By to list all the fields that I would like the user to choose to sort. In this example, I have 3 fields: Sales, Profit, and Average Discount. (Image 2)
Second, the other parameter is Sort Order. I also created a new parameter with the name Sort Order. There are only 2 string values: Ascending and Descending. (Image 3)
Now, we already have 2 new parameters for sorting. Next, we will set a calculation to let Tableau know when users choose an option, what Tableau should do.
2/ Create a Calculated Field for Sorting by Fields
To let Tableau understand how to sort by fields we want, I need to create a calculated field. The method is if I choose the order in Ascending/ Descending and sort by any fields that I listed in the parameter, it will sort sub-categories by Sales in Ascending/ Descending order.
CASE [Sort Order]
WHEN 'Ascending' THEN
CASE [Sort By]
WHEN 'Sales' THEN SUM([Sales])
WHEN 'Profit' THEN SUM([Profit])
WHEN 'Avg Discount' THEN AVG([Discount])
END
WHEN 'Descending' THEN -1*(
CASE [Sort By]
WHEN 'Sales' THEN SUM([Sales])
WHEN 'Profit' THEN SUM([Profit])
WHEN 'Avg Discount' THEN AVG([Discount])
END
)
END
Above is the code that I use for the Sort By Fields calculated field. I used the nested Case. In the first CASE, I use Sort Order because there are only 2 values (Ascending/ Descending). It will save time.
If the user chooses Ascending, there are 3 cases that could happen: the user could choose one field in the parameter (Sales/ Profit/ Avg Discount). Therefore, I put the CASE Sort By nested in the Ascending case.
If the user chooses Descending, I also do the same method but I need to reverse the value by multiplying the aggregated fields with -1. It will help to reverse the value in the original fields from positive to negative and negative to positive. Be careful to put the parentheses when multiplying -1.
Note that:
1/ The value that we put after WHEN should match with the values that we put in the Parameter in Part 1 (The Value column in the Create Parameter window).
2/ The aggregated functions for each field are different. I used SUM for Sales and Profit. But Average for the discount field.
3/ Setting the Sort on the Dimension Field
Now, we have 2 parameters to let the user choose the option and the calculated field to let Tableau what to do if the user chooses. In the last step, I need to set the Sort followed by the field that the user chooses.
First, right-click on the dimension field (Sub-Category) and choose Sort... (Image 5).
Next, the Sort window appears (Image 6). In the Sort By option, I choose Field. In the Field Name option, I choose Sort By Fields (The name of my calculated field in part 2). Then click OK.
Congratulations! Your dynamic sort is done. Let's test it!
In this blog, I showed how to make a Dynamic Sort in Tableau. I hope you enjoy doing it. This is only a simple case but it is helpful to me when there are many fields on my dashboard and I want to let the user choose the field to sort dynamically. If you would like to challenge yourself, just drag a higher level in Rows (For example: Put a Category field before the Sub-Category field in Rows). It would be more difficult if I want the user to choose the Field to Sort. A small hint: You may need to use the RANK function. I used to write a blog about it here.
Thank you! See you in the next blog!