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.
