9 Replies Latest reply on Apr 20, 2012 3:30 PM by Richard Leeke

    Finding an overall rank using averages of calculated ranks

    Jonathan Drummey

      I'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 per-measure 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 (1-N) 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:

       

      screenshot.gif

       

      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

        • 1. Re: Finding an overall rank using averages of calculated ranks
          Richard Leeke

          I only had a very quick read, but I think you're spot on in your assessment of the constraints - not being able to sort or partition by the results of a table calculation I think means you probably can't do this all in Tableau.

           

          It's possible that RAWSQL might get you there by doing the first lot of ranking in the data source. You may well need a data source which supports analytic functions to do that, though.

           

          Sorry to be a bit vague, but it's a while since I did that work on quantiles and I really can't remember all the avenues I explored. I did write up something about what I'd managed to do in SQL on the quantile question before I first got the table calculation version going. You might just get some hints from that posting if you can find it (though you've probably already seen it).

          1 of 1 people found this helpful
          • 2. Re: Finding an overall rank using averages of calculated ranks
            Jonathan Drummey

            Hi Richard,

             

            Thanks for looking at this, I was hoping that you or Joe would be able to respond! I have to use MS Access as the DB, and I'm able to calculate the Provider Rank per Measure and the Average Provider Rank there, though it gets messy because I'll have to create a different datasource with a different set of queries & subqueries for each set of dimensions I want to perform rankings on. I'm currently thinking that I'll still perform the Overall Rank and quartile calculations in Tableau, because I believe it'll be faster to copy those calculations across datasources than to build too many self-joining queries that slow down Tableau***.

             

            I learned today that MS Jet/Tableau is sensitive to what order I bring the query results in, if I bring one of the Provider Ranks into the view before adding all its dimensional components then MS Jet throws an error.

             

            Jonathan

             

            ***  A month or two ago I learned that MS Access queries that do self-joins to large tables can cause Tableau to take several minutes to access the DB every time you want to add a datasource or rebuild an extract, it appears to be something with how Jet is enumerating the tables and queries. Removing those self joins (by creating temp tables that can be joined at runtime) has reduced the time Tableau needs to reach the Access DB to a minute or two, and generally increased performance elsewhere.

            • 3. Re: Finding an overall rank using averages of calculated ranks
              Richard Leeke

              Have you looked in the Tableau logs to see exactly what is taking the time on that slow step?

               

              There are some options for suppressing some of the checks that Tableau does when first connecting to a datasource which can be really well worth exploring in cases like this - Robert Morton wrote an excellent knowledgebase article on data source tuning options. I don't recall if any of those applies to Access though.

              • 4. Re: Finding an overall rank using averages of calculated ranks
                Jonathan Drummey

                Yep, I looked in the logs (and so did Tableau support), the time lag was all when Tableau would go out to access Jet with the query.

                 

                Was this the KB article you were referring to? There are definitely a couple of ideas there of things I could try.

                http://kb.tableausoftware.com/articles/knowledgebase/customizing-odbc-connections

                 

                Jonathan

                • 5. Re: Finding an overall rank using averages of calculated ranks
                  Richard Leeke

                  Was it one of the initial queries to assess the connection before actually executing queries for specific visualisations - that's the area where some of Robert's work has helped me in the past.

                   

                  I think that was the article, though I also recall doing it with non-ODBC connections - I think various flavours of MySQL like Infobright and InfiniDB in my case. It's worth sniffing around to see if there's anything written up anywhere about what you can do in other datasources. If I knew how to insert a mention of another forum user in a post I'd ping Robert to look at this thread - he'd know for sure. (I know you can alert people like that because I've had it done to me, but I haven't seen any explanation of how you do it!).

                  • 6. Re: Finding an overall rank using averages of calculated ranks
                    Jonathan Drummey

                    The problem was even before Tableau was able to run those assessment queries (I'd started with those because that's where I thought the problem was). In moving things around I'd lost track of the particular version of the extract and workbook that had the issue, (and gotten performance to an acceptable level), however I think I'm able to duplicate it at will no:

                     

                    In order to do the ranking in Access for the required sets dimensions I needed to create two more instances of the set of calculations, and over the course of a couple of hours of adding and testing the self-joining queries I watched the time it took Access to do anything involving opening the DB, listing queries, opening these queries, etc. go from a few seconds to several seconds to tens of seconds and a minute or two, and of course Tableau takes a longer time as well. The issue really seems to be something inside Access/MS Jet and not so much Tableau.

                     

                    Jonathan

                     

                    PS: The only way I've seen people pinging others (or gotten pinged myself) has just been someone finding a post that the desired recipient had replied to and then adding a reply.

                    • 7. Re: Finding an overall rank using averages of calculated ranks
                      Richard Leeke

                      OK - hard to comment without seeing it.

                       

                      On the pinging people question - Dimitri managed to ping me on a thread I wasn't on, here: http://community.tableau.com/message/173143#173143.

                       

                      On reflection, I think Joe explained to me how to do that - I think you just paste in a link to the person's profile. Test post coming right up with you as the recipient.

                      • 8. Re: Finding an overall rank using averages of calculated ranks
                        Jonathan Drummey

                        Hi Richard,

                         

                        I spent some time over the last couple days figuring out how to effectively partition using table calcs for a simple case (show a measure for all rows except a single subgroup/partition, in which case show the total of that measure for that partition) in this forum post: http://community.tableau.com/message/177274#177274

                         

                        If you can take a look at it, I'd love to get your feedback.

                         

                        Jonathan

                        • 9. Re: Finding an overall rank using averages of calculated ranks
                          Richard Leeke

                          Well done!

                           

                          It's not exactly point and click, but it gets there. I've done something vaguely similar once or twice in the past, but it really needs a lot of effort and I always find it's hard to understand when you come back to it later (though you've done a fine job of describing it, which will make that easier). I certainly don't know of any easier ways. Of course Joe is still the master at this sort of stuff, even if he has been out of circulation lately.

                           

                          I was just thinking I might need to do something similar to get an answer to come out on this thread which also gets stuck on not being able to partition on a table calculation - though I think there may be a quicker, if slightly grubby way of getting past it in that case.