2 Replies Latest reply: Apr 12, 2012 12:43 PM by Stefan Wühl RSS

    Finding where number fits in range of values

      OK. I am pulling my hair out on this one, but it is probably something easy. I am relatively new at this. Hopefully, I can describe this correctly. Here we go:


      I have a doctors who have visits. Each visit has a score.


      Doctor               Visit                  Measure          Score                    Date

      A                         1                        X                 98.2                    Jan 2012

      A                         2                        Y                 84.3                    Feb 2012

      B                         3                        X                  79.5                    Jan 2012

      B                         4                        X                  92.4                    Jan 2012    

      C                         5                        Y                 74.6                    Feb 2012

      A                         6                        Y                 88.3                     Feb 2012

      A                         7                        Y                 76.4                     Jan 2012



      Based on this type of data, I have a table chart that works great. The user picks the month and a doctor and gets there count and average score grouped by measure.... Here is where the fun begins.


      I also have a table in the database that has the percentile scores such as :


      Month               Measure         99th             90th               60th                30th

      Jan 2012          X                     98                 95                 83                   74

      Feb 2012          X                    85                 83                 75                   66

      Jan 2012          Y                     88                 83                75                   72

      Feb 2012          Y                    90                 88                 82                   76



      What I need to do is show that percentile they achieved (not rounding up). I can do this fine at the individual visit level but figuring this out in the input script and applying the resulting percentile designator to that record. However this doesn't do me any good when I can consolidating visit on the dashboard.


      For example if the user picks doctor A for Feb 2012 I can give them something that looks like:


      Doctor A            Visit           Measure               Score                 Percentile             

                                2                Y                         84.3                    60th

                                6                Y                         88.3                     90th


      But when I try to consolidate this into the following with the single one month, it won't work:



      Doctor A            Total Visit           Measure              Avg Score                 Percentile             

                                2                          Y                         86.3                    --------             




      And what I really need to do is consolidate into more than 1 month and end up with something like: (user picks doctor A and both Jan and Feb and measure Y)


      Doctor A            Total Visit           Measure              Avg Score                 Percentile             

                                3                          Y                         83.0                    60th (based on last month picked)


      In other words, I want to be able to find out what percentile they have achieved when using multiple months rolled up by comparing to the percentiles from the last month chosen. I know this is confusing but pretend I am a doctor and I am looking at my average score over the last three months.... I want a percentile showing where I stand. Since this is more than a single month, I want to compare myself to the range of percentiles which are appropriate for the latest month of the three. Hopefully someone can help me out on this. Thanks.