9 Replies Latest reply on Jul 2, 2018 11:13 PM by Jonathan Drummey

    Double counting members... sometimes!

    Galen Busch

      I'm working with a highly confidential data set, and unfortunately can't provide a workbook. Sorry in advance!



      So here's what I'm working with. A patient comes to a hospital, and they receive a diagnosis, or multiple diagnoses during their stay at the hospital.


      The data would look something like:    


      NameDiagnosisDisease GroupDate
      John DoeC61 - Malignant neoplasm of prostateProstate1/1/2016
      John DoeC20 - Malignant neoplasm of rectumColorectal1/25/2016


      Logic is applied to the chart displayed above to only count the patient once at the disease group of their first encounter. A patient is only counted once - in their first disease group. John Doe, above, would be counted as a prostate patient. Here's the contents of the calculation that generates the disease groups:

      {FIXED [patient id]: max(if [SERVICE_DATE] = {FIXED [patient id]: min(if [Key 4 Flag] = 1 and [Malig Neo meg?] = True then [SERVICE_DATE] end)}

      then [Episode Type] END)}


      Which essentially says; if the date is equal to the first date of an encounter in a key disease group, display which display group that is.


      Now I need to add one bar to the chart - the number of times a patient crosses disease groups. For example, John Doe, above, is both a prostate and colorectal patient. I want to display the number of patients that have more than 1 disease groups in my data set. We call these complex cases, and this is how I've defined them:


      countd({FIXED [patient id] : min(


      {FIXED [patient id] : sum([Key 4 Flag])} >0


      {FIXED [patient id] : countd([Disease Group])} >1

      then [patient id]




      Which says: if a patient is in at least 1 key disease group (key 4 flag) and is contained in more than disease group, display the patient's ID.


      So the problem I run into is that this second calculation falls outside of the dimension groups defined in my first calculation. Any ideas how to get a 5th bucket that contains members from the current 4 buckets? Any sort of if/then logic will only count a patient one time. A patient should display once in their first disease group, and again in this 5th bar if they have multiple disease groups. Thanks!


      Jonathan Drummey

        • 1. Re: Double counting members... sometimes!
          Deepak Rai

          You can Count Disease Groups per person and if It is Greater than 1 flag it as person with Multiple disease

          1 of 1 people found this helpful
          • 2. Re: Double counting members... sometimes!
            Galen Busch

            Hi Deepak,


            thanks for contributing. The issue that I've found with this simplistic approach is that, due to if/then logic, they would only be counted for either single disease or multi-disease, but not both.

            • 3. Re: Double counting members... sometimes!
              Michael Gillespie

              I think this is a "build a dashboard" problem.


              You're going to have a very hard time doing what you want in a single viz.  You should have relatively little trouble building 2 separate vizzes that you display in one dashboard, however.


              Does that make sense?  Or is there a specific reason you have to have just one viz?

              1 of 1 people found this helpful
              • 4. Re: Double counting members... sometimes!
                Galen Busch

                Hi Michael,


                That's a totally valid point!


                Fortunately, I did figure out a solution (I'll post it shortly) - this is a visual aid that is going in a powerpoint presentation, and I was hoping for clean sheet-style formatting, but you're right; there's no reason it couldn't have been a dashboard!

                • 5. Re: Double counting members... sometimes!
                  Michael Gillespie

                  Looking forward to seeing your approach.


                  (And it's kind of cool that someone named Galen is working with medical data...)

                  • 6. Re: Double counting members... sometimes!
                    Galen Busch

                    Alright here's what I came up with.



                    So the calculation that drives the dimension needs a minor modification:


                    if [Key 4 Flag] = 1 then {FIXED [patient id]: max(if [SERVICE_DATE] = {FIXED [patient id]: min(if [Key 4 Flag] = 1 and [Malig Neo meg?] = True then [SERVICE_DATE] end)}

                    then [disease group] END)} elseif not isnull([Complex Cases]) then  'Complex' END


                    The change here being the (If [Key 4 Flag] = 1 then (calculation) elseif not isnull([Complex Cases]) then 'Complex')


                    Now, for the very stupid reason this "solution" works.


                    I took my numbers from my starting point and compared them to my end point for my 4 disease groups - everything reconciles perfectly.


                    So next, I did some validation on my complex bucket - it adds up perfectly.


                    My understanding of my modification to the calculation would suggest that I would get bad data from it - either a patient would end up in a disease group, or complex. But I didn't find this to be the case. The [Key 4 Flag] is essentially just saying - return 1 if the disease group is breast, prostate, colorectal, or lung, and return 0 if it's not. Complex cases could be a breast and prostate patient - which would imply that both disease groups would return 1 for [Key 4 Flag] and would logically only end up in 1 bucket.


                    It works because patients always have data outside of these disease groups. For example, if a patient comes for anything not related to oncology care, they would have a 0 for this [Key 4 Flag]. So by validating these data, I've proven that there is always at least one instance of a 0 value for [Key 4 Flag] for all complex care patients - which makes sense.

                    • 7. Re: Double counting members... sometimes!
                      Michael Gillespie

                      Very interesting, and it illustrates an important point about Tableau.


                      You HAVE to understand your data to get the result you're looking for.  If this data set did not have data other than the 4 disease groups you're specifically looking for, then as you say, this would not work.


                      • 8. Re: Double counting members... sometimes!
                        Galen Busch

                        Sort of a hacky workaround, but if everything checks out, then good data is good data.

                        • 9. Re: Double counting members... sometimes!
                          Jonathan Drummey

                          Hi Galen!


                          The key here is that you want to count a record (the encounter for a patient) more than once in the same viz. Here are the ways I know of to do this in Tableau:


                          - multiple measures

                          - grand totals/sub totals

                          - table calculations e.g. the WINDOW_ and RUNNING_ functions

                          - multiple worksheets on a dashboard


                          Given the requirement to see the counts for individual disease groups and a bucket for patients w/multiple disease groups my initial thought would be to do this either using a view with a custom grand total where the custom grand total would have the count for multiple disease groups or place multiple worksheets on a dashboard.