I've attached the Excel data source so it's a little easier to see what I have and what I'm trying to present.
In a nutshell, I'm trying to take the attached data and summarize, month-by-month, the scores for the ART, CDC and OMW scores (I'm only showing the ART Measure as an example of what I need).
Here is a copy of what I sent my work team's SAS/Tableau support who was not able to find a solution:
The main goal is to analyze numerators and denominators as they are (including Suspicious Diagnoses) vs. what they would be if we Excluded these records.
For the 1st Practice we see lots of MEM_NBR’s but we’re only worried about counting the numerator and denominator. Therefore, for the Exclude option we’re only worried about the sums of art_den and art_num where there are no Suspicious marks. This gives us a 1 for both. If we want to calculate what the numerator and denominator truly are right now because we’re including suspicious diagnoses then we add the numerator and denominator for this one MEM_NBR for this one Practice for this one Measure for this one RPTMNTH which gives us a 1 for Denominator and 0 for Numerator. Now we have two Num/Den ratios of 1/2 and 1/1 for Include and Exclude, respectively.
For the 2nd Practice it gets a little trickier because we see that this MEM_NBR no longer has any suspicious diagnoses but they did have multiple suspicious records in the previous 2 months’ reports. Again, to find what the rates would be if we just excluded these suspicious diagnoses we simply take the sum of the art_den and art_num and get 11 and 9, respectively. Now, we analyze the Include option. If we look at what the 201706 monthly report was assuming we had all of these Practices and all of these Members and we throw in the 1 distinct member who had a suspicious diagnosis in this month our denominator jumps to 12 but our numerator stays at 9 (because they’re all 0’s under art_num). The same is true for this Practice/MEM_NBR/Measure for the RPTMNTH 201709. They had 1 distinct suspicious diagnosis so again we’re just assuming all of these same members and non-suspicious numerators and denominators were exactly the same in previous months and in the 201709 report they also had a denominator of 12 and numerator of 9.
Don’t forget that all non-suspicious MEM_NBRs should count under all RPTMNTHs’ records which leads to the following which is what I want the Tableau sheet to display:
Practice RPTMNTH ART Den Include ART Den Exclude ART Num Include ART Num Exclude
000083153 201803 2 1 1 1
201709 1 1 1 1
201706 1 1 1 1
000084436 201803 11 11 9 9
201709 12 11 9 9
201706 12 11 9 9
From here I will scrunch these into the following which is the final goal of Include vs. Exclude and % Difference.
RPTMNTH Include Exclude % Difference
201803 10/13 10/12 6.4%
201709 10/13 10/12 6.4%
201706 10/13 10/12 6.4%