I have data at the individual level, but for confidentiality reasons I need to create an aggregated dataset that includes only counts of people from broad groupings (e.g., the number of Males who entered prison in 2010, etc.).
The data is complex and I'm not sure how best to reshape it so it's optimized for Tableau.
An example of the individual level data is attached. Each row is a person (a prisoner) along with:
- Year - the year the prison reported the data
- *** - Male or Female
- The prisoner's support system (three y/n variables: Family, Friends, Other)
- The prisoner's cohort (three y/n variables that indicate if the prisoner was 1) in prison at the start of the Year, 2) entered prison during the Year, and/or 3) left during the Year
- some outcomes for the prisoner (whether they were eligible for program X, completed program X, eligible for parole, achieved parole, etc.)
In Tableau, I'd like to be able to show the number and % of prisoners with various outcomes, and filter or group by the other variables
(demographics, support system, and cohort). I'd also like to be able to show the number of prisoners in each cohort, filtered or grouped by Year, ***, etc.
What's the best way to reshape this data to support this? If I was concerned only with Year, ***, and Outcomes I'd probably do something like this:
|Year||***||Outcome||Eligible (Denominator)||Achieved/Completed (Numerator)|
(To get %s, I'd do a table calculation of the numerator/denominator.)
But I'm stumped when it comes to incorporating what is essentially the "check all that apply" items where a prisoner may have one, two, or three support systems, and be a member of one or more cohorts (e.g., those who entered and left prison that year). Aggregated counts of these aren't mutually exclusive - so if I had one row that showed there were 40 prisoners who are Male with Yes for Family Support, and another row that showed me there were 20 Male prisoners with Yes for Friend Support, some of the same prisoners may be represented in both counts. If I tried to show a count for only Male prisoners, Tableau will tell me I have 60 males (40 + 20) which wouldn't be correct.
PrisonerData.xlsx 8.5 KB