A Rank Challenge in Tableau

This blog is about one of the challenges I faced at work when I was dealing with the calculation of a rank in Tableau.

Basically I wanted to show on a map the ranking of country regions both within a country and across all countries and keep to show these values even when filtering on a specific country.

Rank by state within a country: easy, it is just a table calculation compute using on the state.

Rank by region across all countries: it seemed to be easy (but it presents an issue)

I started using an LOD such as {include Country, State: sum(metric)} and then do a rank on this calculation.

rank_1

This was working fine until I started to use filter on country.

When you filter let’s say on Italy, the LOD starts to work as the table calculation, ignoring all the other countries and this actually makes sense, because… you are filtering!

How to go around this?

The trick around this is leveraging the order of operations in Tableau (click here for details about order of operations).

  1. Create a calculated field “New country” LOOKUP(MAX([Country]),0)
  2. Drag this on filter, use all and show filter
  3. Now use this instead to filter on Italy

Now the filter works!

Why?

Basically, if you use a table calculation/ lookup as a “filter”, you will be able to filter the view without filtering the underlying data.

rank_final

What are the limits?

If you want to connect this filter to other charts you do not have the option “Apply to worksheet” in the filter dropdown, because it is a table calculation (lookup) acting in “behalf” of a filter… So it may be not 100% handy solution. It depends on your user case.

Click here to view the dashboard on my tableau public profile.

Another approach would be to calculate this global rank in Alteryx- very easy, just aggregate at the region level, sort descending on the metric and then assign a Record ID. In this way, you do not need to use this lookup calculation.

Advertisements

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