Rank of Rank

Jonathan Drummey
on November 26, 2013

{This is the fourth blog post in the #TableauStatsMonth series, and is a guest post by Tableau Zen Master Jonathan Drummey. Jonathan gives us a basic introduction to Rank, and then dives into a special use case of sorting a list based on average rank across a number of categories.}

I’ve got several favorite new features in Tableau version 8.1: performance improvements, full-screen presentation mode, R integration, and the new built-in rank functions. This post will cover the new rank functions, some ways they can be used and differ from existing functions, and finish up with how they’ve let me replace a whole bunch of SQL, a temporary table, and several different worksheets with a single one-line calculation, and maybe help save some feet, too.

The (blinded) data I’ll be using is based on a real-world use case from the integrated healthcare system I work for. Each month we capture a variety of quality metrics for our primary care providers, things like what % of male patients between 50 and 75 have regular colonoscopy screenings, what % of female patients get mammography
screenings, how many patients with diabetes get their HbA1c levels checked at appropriate intervals, what % of
patients answered 9 or 10 (out of 1-10 scale) on the “Rate your provider” question on patient surveys, and so
on. Each metric has:

  • Measure – the metric’s name.
  • Measure Date – the end date of the period used, in this case the metrics all cover a year’s worth of data.
  • Provider – the doctor or nurse’s identifier, name, etc. The combination of the Measure, Provider, and
    Measure Date uniquely identifies a provider.
  • Numerator – number of patients who got the screening (or answered positively on a survey question)
  • Denominator – total number of patients who should get screening (or answered the survey question)
  • Rate – Numerator/Denominator, as a percentage to 1 decimal place
  • Target Rate – our internally defined target
  • Office Name – the office that they practice in
  • Specialty (aka Service Line) – in this case, there are only two specialties, Internal Medicine and Family
  • HigherRateIsBetter – A Boolean field flagging whether the goal for the measure is a higher or lower rate.

Here are some sample rows:

And the download the data in .xls

Since it’s Movember, let’s focus on mens' health and use the colonoscopy (Colorectal Screening) measure, showing the latest results for each provider:

In Tableau 8.1, if I want to do a ranking of the provider, it’s as easy as clicking on the SUM(Rate) pill, and from the pill’s context menu choose Quick Table Calculation->Rank from the view.

And here’s the view:

The rank is a Standard Competition Rank, so providers Arundel and Jackson are tied at 6 and then rank 7 is skipped, so Bowdoin has 8. If you want to use a different sort of ranking, you can click on the SUM(Rate) pill again, choose Edit Table Calculation…, and then from the dialog change the Ranking type, sort order, and addressing of the table calculation:

So in only two mouse clicks we can get a rank, and with a few more we can alter that ranking any number of ways. Before we do a ranking happy dance, let us pause a moment in honor of the efforts of contributors to the Tableau Calculation Reference Library, in particular James Baker who wrote the complicated formulas for ranking that we’ve had to use until Tableau 8.1.

[Moment of Silence]

Ok, now onto more ranking fun!

Bumps Chart
Since we’re using a table calculation, we can quickly play around with it. Let’s bring the Month into the view and see how ranks change over time:

But text tables aren’t very useful, let’s build a basic bumps chart, showing rank over time. There are 25 providers total, which makes for an overly busy bumps chart, so we’ll filter for just Family Practice.

  1. Drag Specialty from Dimensions window to the Filters Shelf and select Family Practice.
  2. Click on the SUM(Rate) pill on the Text Shelf to set the Compute Using to Provider.
  3. Move Provider from Rows to the Color Shelf.
  4. Move the SUM(Rate) pill to Rows.
  5. Right click on the Rank of Rate axis, choose Edit Axis…, change the Scale to Reversed, then click OK.

To call out individual providers, we can use the highlight feature by clicking on the Color Legend. For example, what’s going on with provider Upton?

To answer that question, we can double-click on the Rate in the Measures window to add SUM(Rate) to the view on an extra axis, and see that Upton’s performance on the Colonoscopy measure has been pretty flat in the past year, while a number of other providers have improved (there are other ways to do this kind of analysis, this is the quick & dirty visual):

If you want to build a full-blown bumps chart, there’s a version in the Top 10 Table Calculations, here’s an alternative layout:

Rank Skips Nulls – Finding the Last non-Empty Value
Thanks to Dan Huff of Tableau for pointing out this useful feature. In the old days (two weeks ago before Tableau 8.1 came out) when we did ranking we were often using the INDEX() table calculation. This could lead to some problems when Tableau was trying to be helpful and padding out the data for us. Here’s an example, using the same colonoscopy measure:

For Camden & Palermo, there are no results for the first three periods, so there are no rates; however, due to domain completion Tableau pads in the empty cells for those three periods and the INDEX() function fills them in. The new Rank function skips them just like I want.

This is really handy for situations where we might want to return the last non-empty value, for example the value of the most recent sale for each customer. You can use rank and sort descending instead of ascending, which brings us to the next section.


An additional option unique to the new Rank and Percentile table calculations is that they also have a sort order built in, so you can choose how the addressing dimension(s) will be sorted. For example, we can easily rank the view from worst to best.

If you had tried to do ranking in Tableau version 8 and earlier, you might be cheering now. Tableau has made what took complicated custom table calculations with often messy computations and sorts every which way into a few mouse clicks.

Ranking from Secondary Data Sources
It gets even better. Here’s something that was much more painful to do prior to 8.1: I’m adding a secondary data source that has a row for each provider and an overall patient panel size. Now I can do the same Rank quick table calculation on the Overall Panel Size:

Ok, what’s so special about this? I’ve just sorted the provider (in the primary data source) by the panel size (from the secondary data source) to generate the rank. Previously we were only able to do this using complicated pill arrangements with relative compute usings, and if any pills moved the whole edifice could fall

Applying Ranking to Table Calculations
Now for the pièce de résistance of the new rank functions, and thanks to Tableau Zen Master Joe Mako for pointing it out: We can even apply the ranking functions, with sorts, on table calculations! Instead of just showing the raw year over year percent improvement for a measure, how about ranking that? For this, we need to use a calculated field, since Tableau doesn’t (yet?) let us do a secondary calculation on a rank.

  1. Start a new worksheet filtering for just Colorectal Screening, and filtering for just the 11/30/12 and 11/30/13 measure dates.
  2. Put the Month/Year of Measure Date on Columns, Provider on Rows, and SUM(Rate) on the Text Shelf:
  3. Click on the SUM(Rate) pill and choose Quick Table Calculation-> Percent Difference. Now we’re seeing the % change in rate for each period.
  4. Click on the SUM(Rate) pill again, choose Edit Table Calculation… bring up the Table Calculation dialog.
  5. Click the Customize… button to open the Calculated Field dialog.
  6. Name the calculation as Rate % Difference and click OK to close the Calculated Field dialog, then OK again to close the Table Calculation dialog.
  7. In the Measures window, right-click on Rate % Difference and choose Create Calculated Field… to open up the Calculated Field dialog.
  8. Enter the formula RANK([Rate % Difference]) and name the field Rank of Rate % Difference.
  9. Click OK.
  10. In the Measures window, right-click on the Rank of Rate % Difference measure and choose Default Properties->Number Format… Tableau will open the Default Number Format dialog.
  11. Set the Number format to be Number (Standard) and click OK. This will keep the ranks whole numbers, where Tableau would in many views add unneeded decimal places.
  12. Drag Rank of Rate % Difference Measure into the data area of the view view over the Rate % Difference measure. Tableau will begin a crosstab:
  13. Drag Measure Names from Rows onto Columns to the right of the Measure Date pill to make the view easier to read.
  14. The ranks of the the % differences are computed across the view, so the ranking is happening across the months, rather than by provider. To get to that, we’ll need a nested compute using. Right-click on the Rank of Rate % Difference pill, then choose Edit Table Calculation… to bring up the Table Calculation
  15. Set the Compute Using for Rank of Rate % Difference to Table (Down), or Provider if you prefer to set a fixed compute using/addressing:
  16. Click OK. The view now looks like this:

We’ve just sorted the Provider dimension within each Month by a table calculation – the Rate % Difference – and generated a competition rank on that. Sorting a dimension by a table calculation for use in another table calculation wasn’t possible in version 8.0 and earlier.

What if we wanted to sort the view on this rank? In the old days (prior to 8.1) we’d create another calculated field to return a -[Rate % Difference], make it discrete, put it in the view, and set up more filtering to get rid of the extraneous values. Lots of mouse clicks there. With 8.1 we can get the same results in only 7 clicks.

  1. Control-click on the Rank of Rate % Difference pill on the Measure Values Shelf to drag a copy to the Filters Shelf, then choose Special->Non-null values and click OK. This dynamically filters out the first month and the non-ranked providers.
  2. On the Measure Values Shelf right-click on the Rank of Sales % Difference pill and make it Discrete(blue). This is a great trick for converting measures into discrete values to use as a header.
  3. Drag the Rank of Rate % Difference pill from Measure Values onto the Rows Shelf to the left of the Provider.

Putting It All Together
Awhile back our vice president for quality called me into his office and asked me to take this data, rank each provider’s performance for a given metric, then take the average of all of those ranks to compute an overall rank. In other words, we were finding out who were the best providers at meeting a basket of metrics. And, oh by the way, compute those ranks at different aggregation levels – within the office, specialty, and across all the providers. This was one of those two minute requests that took a lot more than two minutes to deliver!

Using the INDEX() table calculation in Tableau, I could generate a rank for each provider and metric, and compute an average of the ranks. However, when it came to generate an overall rank I was stuck because I couldn’t sort the providers by the average of the ranks, since that was a table calculation and (prior to 8.1) we couldn’t sort dimensions by table calculations.

So I ended up doing the first level of ranking in the database with a whole bunch of SQL, in fact a bunch of SQL for each aggregation level, then UNION’ed those results together, dumped them into a temporary table, and used that table as the Tableau data source. Then I computed the overall rank inside Tableau, and given issues with unwanted domain completion there was a lot of complexity (and fragility) in the views, here’s one of them:

The view can be read in a couple of ways. Looking across, we can see the rank for a provider and how well they are doing. Doctors can be pretty competitive, so they don’t want to be at the bottom of this list – which is shared with all the providers – and work to improve their performance. The docs at the bottom of the list who have empty spaces are new providers who haven’t been in the system long enough to have all of their metrics generated, so they get some slack (and encouragement).

Looking down the heatmap, we can see the rank of each provider for each metric. White numbers mean the target for the metric has been met, numbers in red are failures to meet the target. So someone might be one of the top-ranked providers on a given metric, but still not meet our target. One example is foot examination, at the time of the screenshot above we weren’t doing as well as we wanted on that metric, which is a measure of how many patients with diabetes get regular foot examinations. Diabetes can lead to nerve damage in the extremities, which can lead to infections and ulcers that lead to amputation, amputation in elderly folks can lead to them needing much higher (and more expensive) levels of care, and earlier death. So just doing a regular foot examination can save feet, and save lives. The good news is that this is an old view, our performance has
improved quite a bit since then.

Building the Heatmap
Given what we’ve covered so far, building this view is fairly straightforward, with one little twist. One of the measures is HbA1c > 9, which measures how well diabetes is being controlled. Unlike all of the other measures, the goal of this measure is to have a lower rate, indicating that there are few patients with HbA1c > 9. To get the Rank calculations to sort properly, we need to change how the rates are done for this measure, which is why the HigherRateIsBetter field is in the data. Read on to find out how.

  1. In the Dimensions window, right-click on HigherRateIsBetter and choose Create Calculated Field… The Edit Calculation dialog opens. Name the calculation “Rate for Rank” and give it the following formula:
  2. Drag Measure Date onto the Filters Shelf, and Filter for 11/30/2013.
  3. Drag Provider onto Rows, Measure onto Columns, and Rate for Rank onto the Text Shelf:
  4. Right-click on the SUM(Rate for Rank) pill and choose Quick Table Calculation->Rank to build a Rank table calculation.
  5. Right-click on the SUM(Rate for Rank) pill again and choose Edit Table Calculation… to open the Table Calculation dialog.
  6. Set the Running along (addressing) to Provider.
  7. Click the Customize… button to open the Calculated Field dialog.
  8. Change the calculated field Name to Provider Rank for Measure and click OK to close the Calculated field dialog, then OK once more to close Table Calculation dialog. The view should look like this:
  9. To build the desired view, we’re going to use Show Me and pick the Highlight Table:

    Tableau rearranges the pills on Rows & Columns and generates the highlight table:
  10. Swap Provider and Measure on Rows & Columns.
  11. Ctrl+Drag a copy of the Provider Rank for Measure pill from the Marks Card to the Filters Shelf, then choose Special->Non-null Values. This stops Tableau from filling in the cells with no Rank with a color.
  12. Now to set up a quartile based color palette. Since the scales here are linear, we don’t have to use any percentile functions, we can just use the Tableau colors. Click on the Color Legend menu and choose Edit Colors… The Edit Colors dialog opens.
  13. Change the Palette to Blue, set it to be a Stepped Color with 4 steps, and click on Reversed, then click OK:
  14. Now we’ll create an average rank across all the measures for each provider. In the Measure window, rightclickon the Provider Rank for Measure calculation and choose Create Calculated Field… The Calculated Field dialog opens.
  15. Name the measure “Average of Provider Ranks” with the formula WINDOW_AVG([Provider Rank for Measure]), then click OK:
  16. In the Measures window, right-click on the Average of Provider Ranks field and choose Convert to Discrete.
  17. Drag the Average of Provider Ranks pill onto the Rows Shelf, to the left of Provider. The default Compute Using of Table (Across) here works fine.
  18. Now to build the overall rank. In the Measures window, right-click on the Average Provider Rank field and choose Create Calculated Field… The Calculated Field dialog opens.
  19. Name the measure “Overall Provider Rank” with the formula RANK([Average of Provider Ranks],’asc’), then click OK:

    It’s necessary to set a sort order here within the calculation because a lower rank is better.
  20. In the Measures window, right-click on the Overall Provider Rank field and choose Convert to Discrete.
  21. Drag the Overall Provider Rank pill onto the Rows Shelf, to the left of the Average of Provider Ranks. You’ll see just a single value of 1.
  22. Right-click on the Overall Provider Rank pill on the Rows Shelf and choose Edit Table Calculation… The Table Calculation dialog opens.
  23. Set the Compute Using to Provider, then click OK.

We’re not using the context menu to set the Compute Using because that sets the Compute Using for the chosen calculation and all of its nested calculations, in this case we need the original Provider Rank for Measure to be computed along Provider, the Average of Provider Ranks to be computed along Measure – which is what the Table (Across) does in this pill arrangement – and finally the Overall Rank to be computed along Provider. Here’s the view:

We can see that provider Temple has the best performance across the basket of measures, however since Temple is missing several results it’s pretty obvious that Temple is a new provider, and in our experience those first several months can have wide swings as the patient panel fills out. A huge advantage to me with Tableau 8.1 is that I can exclude Temple with two clicks and everything will instantly update, whereas in my prior view I had to do the exclusions back in my SQL code, then re-generate the temporary table, and rebuild the Tableau
data extract.

Besides filtering individual providers, using the same view I can filter for the different levels of aggregation – all providers, by specialty, by office, so instead of needing a ton of worksheets to show all of them I can simplify them. The best part is that I don’t need all that SQL code or temp table anymore, they are replaced by two lines with RANK() functions. During the Developers on Stage portion of the Tableau Customer Conference keynote, Melinda Minch said that the Tableau developers like to give us (users) nice things, they certainly have!


Hi Jonathan,

Nice work, however I have a bit of a problem with a similar type of query.

I am looking to create a bump chart for the Rank of each Brand's 'Top Consideration %', for each month.

Normally this would be fine - I would rank the 'Top Consideration %' and 'compute using' brand.

However the consideration value is itself a percent of total sum(count)/window_sum(count) computed using a separate field - 'Top Consideration/Bottom Consideration'

Therefore every time I try to rank my consideration computed by brand, it removes the consideration's percent of total computed by 'Top Consideration/Bottom Consideration' and is effectively ranking sum(count)/window_sum(count) 'computed using' brand - no longer 'Top Consideration/Bottom Consideration'

How can I rank by brand but reserve my percent of total ranking by 'Top Consideration/Bottom Consideration'

I can't use a static 'Top consideration %' because I'm using multiple filters on the dataset.

Any ideas? Thanks.

Add new comment