Parameters, the functionality of Tableau that users love. Parameters are dynamic values that replace the constant values in all the calculations. So, the user can change them on a dashboard or a simple worksheet with the help of interactive control. You can now understand why users rate this functionality of Tableau among the top ones. By adding a parameter to your dashboard or worksheet, you open a lot of opportunities to the users. Furthermore, combining parameters with Calculated fields enables your workbook or dashboard to become more user-friendly since it adds a whole new set of additional interactivities. What makes Parameters interesting as they are is their ability to upgrade the already existing Tableau built features to the next level. We will see how we can enhance sorting with the help of Tableau parameters.

Read more: Tableau Date Functions Complete Guide »

This article is all about showing the main concept of how you can level up the functionality of your workbook by adding parameters to the already existing features. We will also go through a tutorial that will help you implement user-friendly interactive control on your worksheet or dashboard that will enable your users to change the sort measure or order the view as they want. Having clear concepts of parameters is valuable for the Tableau Data Analyst Certification as well.

Let us dive into it. 

The Idea

For the sake of the tutorial, we will use the Superstore Sales sample data that is completely free and comes with each Tableau installation. Let’s create a simple view that will preview the Profit, Sales, and Profit per Customer, all grouped by States. We will preview the Profit and the Sales by States with a simple bar chart, while for the Profit per Customer we will use the color-coded tornado chart. Just a quick reminder. Tornado charts are also bar charts but a special version. Instead of the standard horizontal presentation of data categories, here the categories are listed vertically. Since the categories are ordered so that the largest one appears on the top, the second-largest beneath it, and so on, the final chart looks like a tornado from where it got its name. In Tableau, it is more than simple to create such a chart. Under Multiple Marks shelf, we just need to place [Customer Name] on the Level of Detail card and [Profit] on Color. Voila, we got our tornado chart. 

Once we have created our view, it is time to start analyzing the data that is available for us. We can see that the bars are not sorted out, they appear in random order. We want to apply sorting. For a start, we can use the built-in features from Tableau regarding sorting your data. Keep in mind many of these topics are important for the Tableau Desktop Specialist Certification Exam. In Tableau, there are many ways how you can sort your data. You can do the sorting with just one click on the sort buttons on a worksheet or a dashboard. This is more of a temporary solution that will enable you to see your data sorted right away. Or you can do a more persistent sort, which enables you to sort your view by data order, alphabetically, regarding a specific field, or manually. However, all of these sorting features are not enough if you are doing a very in-depth analysis. 

Keep reading: Tableau Desktop Specialist Certification Questions »

The easiest way to start sorting your data categories is by simply selecting one of the measures (for example, Sales) and clicking on the axis. Then, you just need to use the sort buttons on the Tableau toolbar. This will work well for all three measures. But for example, sorting the bars simply like this for Profit per Customer measure will just sort the States by the Total Profit, not by the Profit by Customer. To clarify, you will achieve the same sorting as you did for Profit.

But what if the idea of your analysis is to begin sorting these bars by profits and losses only? 

Fortunately, this is available In Tableau.

You first need to create two simple calculations. One for the profits only, and one for the losses only.

The calculated field for the Profits only should be:

MAX([Profit],0)

While the calculated field for the Losses should be the total opposite of the one above. It should be:

MIN([Profit],0)

So now, once we have created the calculations, we can use them for sorting the bars. To set the sorting order of [State] to be by Profits only, we should choose Sort by Field and then select the field that we have created for Profits only. Or if we want to sort the bars by losses, we need to choose the fields that we have created for Losses. 

We have attached a screenshot how does the view look like, sorted by [Profits only].

And also, how does the view look like, sorted by [Losses only].

The drawback is that this is only applicable on the worksheet itself, not on the dashboard. For the users of the report, it will be pretty inconvenient to keep changing the sorting measure and the sorting order constantly while interacting with the view. Or even worse, they won’t be able to change it at all if you’re previewing only the dashboard to them. 

Keep on reading: Tableau Data Analyst Certification Questions »

Wouldn’t it be awesome, if this feature is dynamic and the sort measure and the sorting order can be changed constantly, to fit the needs? Wouldn’t it be more convenient and user-friendly if the user is offered an interactive control available on the dashboard? This is why it is valuable for the Tableau Data Analyst Certification.

Of course, that is what this tutorial is all about. 

By combining Parameters and some Calculated Fields, you can implement interactive sorting control on your dashboard that will enable the users to see the data as they want. 

Let us take it step-by-step.

Why did we say we can implement this easily? Because we need only one Parameter and only one Calculated Field for an interactive dynamic sorting feature.

First, we need to create a Parameter.

Right-click on the data pane and choose Create Parameter. Give some name to the Parameter that will indicate that this Parameter will be used for sorting the measures, for example ‘Sorting States by Measure’. As a data type of the Parameter choose String. Below choose List and enter the names for all measures that you want to sort the states by. To make this Parameter even more efficient and able to be used for managing the sorting measure and also the sorting order, we need to make this Parameter applicable for both entries. That is why for each measure we will have two rows, each of them representing the measure and the relevant sort order, “ascending” or “descending”. To make this Parameter even more visually representable, you can use the triangles symbol that will represent the sort order. That being said, ▼ will represent descending order, and ▲ will represent ascending order.

We need to create a Calculated Field that will indicate the Sort Measure

Once we have the Parameter created, we should create a Calculated Field that will be in the background for the Parameter. In order words, this Calculated field will indicate what measure and what sorting order should be taken into consideration when chosen in the interactive control that we created above. The formula that we use in the Calculated field is a simple CASE WHEN statement. Extra hint. Measures are always sorted in ascending order, by default. In order to switch it to descending sort order, we just need to set the measure to be a negative value.

The formula for this Calculated field should look like this:

CASE [Sorting States by Measure] WHEN ‘Profit ▼’ THEN -[Profit]WHEN ‘Profit ▲’ THEN [Profit]WHEN ‘Profits only ▼’ THEN -[Profits Only]WHEN ‘Profits only ▲’ THEN [Profits Only]WHEN ‘Losses only ▼’ THEN -[Losses Only]WHEN ‘Losses only ▲’ THEN [Losses Only]WHEN ‘Sales ▼’ THEN -[Sales]WHEN ‘Sales ▲’ THEN [Sales]WHEN ‘State’ THEN 0END

Protip

One thing that you should always take care of is to use the exact same names in the WHEN statement as you defined them while creating the Parameter. If the names between the Calculated field and Parameter differ one from another, the Parameter will not be able to evaluate what measure and sort order should be taken into consideration, and as a result, we will get an invalid Parameter.

To connect the two previously explained, by setting the Sort Order of the Dimension

The most important thing about combining parameters with some of the already existing functionalities of Tableau is to understand how they work in the background. The parameter is just an interactive control that is shown on the dashboard and can be easily accessible by the user, while the Calculated field behind is the one that is evaluating the option that the user selects from the parameter. And then, the Calculated field assigns it a proper action. To clarify, we have the Dimension that we wish to sort regarding our needs at the beginning, a Parameter at the end of the cycle, and a Calculated field in between that is connecting the dots. So, since we have connected the Parameter and the Calculated Field, it is time to connect the Dimension in our case, [State] with the Calculated Field. 

We click on the arrow of the pill [State] on the Row Shelf and choose Sort. A dialog box regarding the sorting will pop out. This time, we should choose Sort by Field and choose the Calculated Field that we have created previously. 

Show the Parameter on the Dashboard

It is time to enjoy the fruits of our work. The most satisfying moment, showing your parameter on the Dashboard and trying it for the first time. 

To show it, we need to right-click on the Parameter that we have previously created, ‘Sorting States by Measure’, and select Show Parameter control. 

That is it.

So simple as that, we just needed four steps to create an additional drop-down list that can change the sort measure and also, the sort order of the view in just one click. 

Create an Enhanced Version

The previously explained example was a simple version of how we can implement an interactive control to the Sort feature of Tableau by combining two conditions in one Parameter – Sort Measure and Sort Order. As a result, we have created a Parameter where we had two entries per each measure, one that will present the measure in a descending order, and the other one in ascending order.

Since we have learned how to do the simple version of it, it is time to upgrade our knowledge and make this view a little bit more sophisticated by adding another Parameter which we will use to select the sort order independently.  So now, as a result, our view will have two Parameters that can be used to sort the States regarding our needs. 

First, we will create a Parameter for the Sort Measure only

This step is pretty much the same as the first one in the simple approach. Right-click on the data window, choose Create Parameter, and give it a name that will indicate that this Parameter will be used only for the Sort Measure. For example, [Sort States by Measure]. As a data type of the Parameter choose String. Below choose List, and enter the names for all measures that you want to sort the states by. 

The only difference between this Parameter and one in the basic approach is that we have only one entry per measure in the list of values.

Now, create another Parameter for Sort Order

This is the new step that differs this approach from the previously explained one. So, we repeat the same process of creating a Parameter from the previous. Again, as a Data type, we choose String. In the previous Parameter as a list of values, we have listed all the measures by which we want the dimension sorted. Here, we will have a list of only two values that will indicate the sort order: ascending and descending. We will give this Parameter a meaningful name in order to make a distinction from the previous one. For example, [Sorting Order of Measure].

To transform Text field into Number, Create a Calculated Field

Since we aim to sort the view ascending or descending not only by the measures [Profit] and [Sales], but also by the dimension [State], we need to do an additional step and transform the names of the states into a number.

We are aware that regarding this step there may be a few ways of doing it, but one possible way of doing this is with the help of the ASCII String function. Regarding the ASCII String function, it works by returning the relevant ASCII code for a certain string expression. 

Now, create a Calculated Field, whereby using the ASCII function, we can convert the first four characters of the State Name to a relevant number. One important thing is to multiply the characters with decimals, so we can create a four-digit number for each state name separately. The decimals should be in descending order and depending on how many characters of the string we are taking into consideration. Since our formula is taking only the first four characters, we are multiplying it by 1000, then 100, 10, and 1. Additionally, we are using the MID and FIND functions that can help us extract the relevant character of the string and transform it into a number. Once created, we can use this ordinal number to sort the States by descending or ascending order.

The field should look like this:

ASCII(LEFT([State],1))*1000 +ASCII(MID([State],2,1))*100 +ASCII(MID([State],3,1))*10 + ASCII(MID([State],4,1))

We are aware that we are doing it ‘the hard way’ by using this method for transforming the text fields into numbers, but in most cases, it works. Still, this method has its drawbacks and sometimes may not work correctly 100%. For example, we have states that have the same first four characters, for example, North Carolina and North Dakota. 

Create a Calculated Field to Connect Both Parameters

As we explained in the basic approach above, we need a Calculated Field to serve as a mediator between the Dimension we want to sort out and the Parameters that should enable the user to interact with the view and sort it based on the needs. The process of how we create a Calculated field and its meaning here is pretty much the same as in the step in the basic approach, but the calculation that we will be using is different. Having a good understanding here is helpful for the Tableau Desktop Specialist Certification Exam.

We will be using CASE statement again so we can evaluate which option was chosen in the Parameter so it can be assigned a proper action. But now, we will add additional IF clauses for every WHEN statement. The IF clause will determine if the sort should be ascending or descending and order it the right way. Also, since we have transformed the state names from text fields into ordinal numbers, we have to use aggregations here as well in order to make this formula functional. We will be using SUM and AVG. 

CASE [Sorting States by Measure]WHEN ‘Profit’ then IF [Sorting Order of Measure] = ‘Ascending’ then SUM([Profit]) ELSE -SUM([Profit]) ENDWHEN ‘Profits only’ then IF [Sorting Order of Measure] = ‘Ascending’ then SUM([Profits Only]) ELSE -SUM([Profits Only]) ENDWHEN ‘Losses only’ then IF [Sorting Order of Measure] = ‘Ascending’ then -SUM([Losses Only]) ELSE SUM([Losses Only]) ENDWHEN ‘Sales’ then IF [Sorting Order of Measure] = ‘Ascending’ then SUM([Sales]) ELSE -SUM([Sales]) ENDWHEN ‘State’ then IF [Sorting Order of Measure] = ‘Ascending’ then AVG([State Name Number]) ELSE -AVG([State Name Number]) ENDEND

Written like this, the CASE statement may look a bit complex since it is containing all IF statements within all the WHEN statements. If you have trouble reading or understanding this, there is a way how to simplify this. 

We will create another calculation that will be related to the sorting order and instead of stating the order, we may use a sort sign to make this more simple.

IF [Sorting Order of Measure] = “Ascending” THEN1ELSE-1END

Now we can rewrite the formula above more clearly. 

CASE [Sorting States by Measure]WHEN ‘Profit’ then [Sort Sign] * SUM([Profit]) WHEN ‘Profits only’ then [Sort Sign] * SUM([Profits Only])WHEN ‘Losses only’ then [Sort Sign] * -SUM([Losses Only])WHEN ‘Sales’ then [Sort Sign] * SUM([Sales]) WHEN ‘State’ then [Sort Sign] * AVG([State Name Number]) END

The outcome of both calculated fields is the same. The only change that we have made is to replace the IF THEN statements in the calculations with another Calculated Field that serve the calculation for choosing the sorting order. Now the calculation looks simplified and it is easier to be understood. 

Place the Sort Order of the Dimension on the Row Shelf

This is the exactly the same process as we explained in the basic approach. We click on the arrow of the pill [State] on the Row Shelf and choose Sort. A dialog box regarding the sorting will pop out. This time, we should choose Sort by Field and choose the Calculated Field that we have created previously.

Again, the most satisfying moment. Show your Parameter!

But now, the feeling is double. You have to show both your Parameters: [Sorting States by Measure] and [Sorting Order of Measure]. Just a design hint, you can set the [Sorting States by Measure] as a compact list and the [Sorting Order of Measure] as a single value list.

Voila! We just added two extra steps and created something amazing. 

Pitfalls and drawbacks of this technique

The whole technique that we described above is a workaround. But it is worth it If you want to give your dashboard what every dashboard needs – interactivity.

Similar to any other workaround techniques, this one has its drawbacks as well. First, it requires not a lot, but still some time and effort. Also, you need to state the measures that you want your view sorted by in front, which sometimes can be a little inconvenient. But even if you change your mind regarding some measures, you can easily change it. 

The greatest pitfall of this technique is that it works only if the user didn’t previously apply the built-in sorting by Tableau. If you or your user sort the measures by using the sort buttons on the toolbar, our dynamic sorting won’t work. This is because, when you use the sort buttons in the toolbar, the sorting is set to be manual. And for this technique to works, the sorting needs to be set directly on the dimension in the Row Shelf, to Sort by Field.

That being said, this technique won’t also work If you are using the persistent sort on the worksheet itself. By persistent sort, we mean sorting the view by data source order, alphabetic sort, another field, or to manual sort.

As long you are aware of the drawbacks and pitfalls of this technique and you inform the users of the dashboard accordingly as well, everything will work smoothly. 

Try this technique on your own and let us know your feedback!

---

Get our Most Popular Downloads

Download the most popular scenario-based Tableau Workbooks in .twbx format. Used by thousands of Tableau developers and job aspirants every day to improve and fine-tune their CV and Tableau Public profile. Join the largest Tableau Experts Social Group.