Finding an overall rank using averages of calculated ranks
Jonathan Drummey Apr 12, 2012 11:57 AMI'm calculating a set of ranks per provider (physician) in Tableau, and averaging those ranks. What I'd like to do next is find an overall rank per provider, and be able to calculate quartiles.
Here are the details:
We have a set of performance metrics for each provider. What % of their heart failure patients have a diastolic BP < 140, what % of their diabetes patients have an HbA1c measurement of < 7, etc. I've been asked to create an overall rank for each provider, based on ranking their permeasure performance and then averaging that rank, as well as identify the quartiles they fall into.
I've gotten as far as creating an Average Provider Rank that is an average of the provider's ranks (1N) for each measure, by using the following table calc to identify the Provider Rank per Measure (in order to preserve ties):
IF ATTR([Score for Sorting]) = LOOKUP(ATTR([Score for Sorting]),1) THEN
PREVIOUS_VALUE(0)
ELSE
PREVIOUS_VALUE(0)+1
END
The Score for Sorting is an adjusted version of the Score because some measures like HbA1c > 9 have lower numbers = better performance.
Where I'm stuck is that I can't figure out how to compute an Overall Rank, nor how to compute the quartile. It seems like any Overall Rank I compute has to be able to compute along the Average Provider Rank, however since that's the result of a table calculation it's not available. As for computing a quartile, Richard Leeke's TCRL quantile functions and other percentile functions I've seen all require INDEX() to be available, and that has the same problem since INDEX() can't be partitioned by the results of a table calculation.
The picture below shows a rough idea of what I'd like to accomplish, using the sample data:
I know I could export the results and then use those as an input in Tableau/Excel/etc. to get the Overall Rank and quartile. I'd like to do all of this in Tableau, is there a way to do it? From some of the reading I've done it seems like RAWSQL might get me there?
Jonathan

ranking test.twbx.zip 141.7 KB