4 Replies Latest reply on Aug 30, 2012 1:23 PM by Hannah Moore

    Calculation to group / find patterns

    Hannah Moore

      In my data source I have a field (financial class) that I would like to group by zip code in order to find patterns.  The problem is there could be many combinations of financial class, and I would like to see if there is a way to use a formula to group records into the appropriate combinations of financial class.  In the attached example, I would like to use zip codes to group into financial class.

       

      For example for zip code 15206 this would be a "Commercial and Self-Pay" group, and zip code 98006 would be a "Commercial, Medicaid/BHPP HO and Self-Pay" group.  Then I could use those groups to get a count of zip codes that fall into those groups.

       

      This may be a simple solution that I just can't seem to wrap my brain around, but hoping someone has an idea of a way to do this!

       

      Thanks,

      Hannah

        • 1. Re: Calculation to group / find patterns
          Tracy Rodgers

          Hi Hannah,

           

          I'm not sure I completely understand the desired outcome. For 15206, how do you know that Self-Pay is supposed to be part of the Financial Class, and for 98006, how do you know Commercial is included?

           

          -Tracy

          • 2. Re: Calculation to group / find patterns
            Hannah Moore

            Hi Tracy,

            Sorry for not being clear, I hope this clarifies what I'm looking for:

             

            The data source is set up so that each row is a combination of customer, zip code and financial class.  The same customer could have multiple financial classes.  The idea is, I want to know how many customers have each unique combination of financial classes.  Since there are 5 options for financial class, there could be 100+ combinations.  Writing a formula for each would be very time consuming!

             

            For this example, I aggregated the data source by zip code (trying to simplify) - so that same data structure applies.  The data source is set up like this:

             

            zip code  financial class

            15206     Self Pay

            15206     Self Pay

            15206     Commercial

            98006     Commercial

            98006     Medicaid/BHPP HO

            98006     Self Pay

            98006     Commercial

            98006     Commercial

            etc....

             

            So the reason I know that for 15206 "Self Pay" is supposed to be part of the financial class is because that record exists in the data set - if there was no 15206 "Self Pay" field, it wouldn't be.  I hope that makes sense and thanks for your help!

            • 3. Re: Calculation to group / find patterns
              Robin Kennedy

              I see what you're trying to do... kinda a 5 dimensional venn diagram to see how many records in each unique combination (there are 31 combinations)

               

              If it were me, I would first aggregate your data some more so you'd have:

               

              15206  Self Pay  2

              15206  Commercial  1

              98006  Commercial  3

              98006  Medicaid  1

               

              etc...

               

              Then create a truth table for each zip and convert back to your 31 individual strings

               

              i.e.

                             Self Pay    Commercial    Medicaid   Charity

              15205      1               1                    0               0           

              98006      1               1                    1               0    

               

              1100 would be "Self Pay, Commercial", 0101 would be "Commercial, Charity" etc.

               

              Probably easiest to do this in Excel then chuck the results back into Tableau.

              • 4. Re: Calculation to group / find patterns
                Hannah Moore

                Thanks Robin.  Yes, that's what I ended up doing for this project.  But I would still love to see if anyone has any great ideas for how to do this more easily using Tableau.  I have a few projets that would benefit from this type of analysis, so all suggestions appreciated!