Power to the user – Parameters part 4 – Top N and Sorting by

Welcome back to the “Power to the user” series of posts about parameters in Tableau.

In this blog post I will write about dynamic “top n” and dynamic sorting with parameters.

[If you missed the previous blog posts, click here for the first one of the serie, here to read about what if analysis and here to read more about dynamic dimensions and measures]

First thing, understand when & why you want to do something like this

In this was the end user will decide how many categories/ items to display in the view and also if you have multiple measures involved how to sort based on the favourite measure.

An example of application below.

DYNAMIC TOP N AND SORTING IN BAR CHART

This example uses Sample Superstore data from Tableau.

  1. I want to build a bar chart with dynamic top N based on Product Name and sorting based on
  • Sales
  • Profit
  • Quantity

Also I want the Top N to update on the selected metric so it will show the top N of product based on the metric of the sorting.

Let’s start.

Top N parameter: 

2. First, build the view as below, dragging Product Name into Rows, Sales, Quantity and Profit into Columns as in the image below

bar_chart1.PNG

3. Right click on Product Name in columns and select the filter option, then go in Top and select your favourite field to compute the top N on, then click on the dropdown and select the option “create a new parameter” as below

Top N

4. Configure the parameter “Top N” as you prefer, I made it showing by default 10 results, with minimum range 5 and maximum 200 and step size 5

Top N2

5. Click ok. Now the user will be able to display a dynamic top N using the parameter in the top right corner of the worksheet based on sales:

Top N3

Sorting by parameter (and connecting the top N to the measure you are sorting on):

6. As we want to be able to sort base on sales, quantity and profit, we need to go ahead and create a parameter

Sortby parameter.PNG

7. The parameter “sort by” will appear in the bottom left of the view as in the image below, just right click on it and select “Show parameter control” so you will have it in displayed in the top right corner of the worksheet:

parameter_2

8. Now we need to enable the parameter because now it is doing nothing! Create a calculated field which says:

case [Sort by]
when ‘Sales’ then sum([Sales])
when ‘Profit’ then SUM([Profit])
when ‘Quantity’ then SUM([Quantity])
END

9. Right click on Product Name which you are using in Rows and select “Sort”, then configure the sorting based on the new calculated field you just created and descending as below:

config_sortby.PNG

10. This is your view now:

almost_end.PNG

11. One last thing to do, if you want also to link the Top N based on the measure you are taking into account with the sort by parameter, just go back in the filter (right click, edit filter, top) and select the calculation of the sort by. This will update the top N based on the metric of interest.

END.PNG

Hope you enjoyed!

Advertisements

One thought on “Power to the user – Parameters part 4 – Top N and Sorting by

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s