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

# Calculation to group / find patterns

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

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

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

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

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!