3 Replies Latest reply on Sep 13, 2018 7:10 AM by Matthew Peterson

    Matt P -- Suspicious Diagnoses

    Matthew Peterson

      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%

        • 1. Re: Matt P -- Suspicious Diagnoses
          Yuriy Fal

          Hi Matt,


          I had taken a look, but couldn't quite understand

          the counting logic which comes to the final digits.


          What are the exclusion rules? Should one exclude

          the [Suspicion Level] = '1. High' Records

          or the 'suspicious' [Mem Nbr] Dimension Values?


          What are the inclusion rules (if any)?


          Sorry for my dumb questions.




          • 2. Re: Matt P -- Suspicious Diagnoses
            Matthew Peterson

            Hi Yuriy. They're not dumb questions at all.


            The Suspicious Level really has nothing to do with what I'm looking at. All I care about is each Practice's performance for each Measure if we INCLUDE the Suspicious Diagnoses vs if we EXCLUDE the Suspicious Diagnoses. Whether or not each member (MEM_NBR) has a suspicious diagnosis can be determined in the Measure and RPTMNTH fields.


            Although they're not unique in this data set we only want to count one suspicious diagnosis per member. We also only want to analyze, measure-by-measure the SUM of the numerators and denominators, again including vs excluding these unique suspicious diagnoses.


            So, for the 1st practice (000083153), for the ART Measure, for the RPTMNTH of 201803, if we're looking at the EXCLUDE metric they only have a 1 for both numerator and denominator. If we look at the INCLUDE metric we still have a 1 for numerator and we have a 2 (not a 3) for denominator. Therefore, their score is 1/1 if we exclude and 1/2 if we include.


            For Practice 000084436 (ignore the OMW measure), we're still looking at 1 distinct suspicious member within the practice but this member was "supsicious" in each of the reports created in June 2017 and September 2017 (they are no longer suspicious so it's been fixed by our team). To analyze this, the denominator is 11 and the numerator is 9 for all three RPTMNTHs (201706, 201709, 201803) if we are EXCLUDING. If we are INCLUDING then the 201803 numerator and denominator stay the same and the numerator for 201706 and 201709 remains at 9 but the denominator for 201706 and 201709 becomes 12.


            Although the FINAL table below appears to have no changes or minimal changes we can see by looking at each Practice and each month that there are certainly changes and these will be obvious when we're looking at all of our Practices. There will also be 6 more columns in the final table, as right now we're only looking at the ART Measure.


            I hope this helps and thanks again!




            Practice 000083153

            RPTMNTH     Include     Exclude

            201803          1/2               1/1

            201709          1/1               1/1

            201706          1/1               1/1


            Practice 000084436

            RPTMNTH     Include     Exclude

            201803          9/11               9/11

            201709          9/12               9/11

            201706          9/12               9/11



            All Practices (this will be the actual table I show in my dashboard)

            RPTMNTH     Include     Exclude     % Diff

            201803          10/13             10/12        6.4%

            201709          10/13             10/12        6.4%

            201706          10/13             10/12        6.4%

            • 3. Re: Matt P -- Suspicious Diagnoses
              Matthew Peterson

              Question answered simply by editing SAS Output.