(Sample data with pivot table is attached.)
I have raw data arranged like below. The Count field in each row shows the number of prisoners who paroled or died, by State, Year, Gender, and the Year Interval when it occurred (0-1 yrs, 2-4yrs, 5-6 yrs). (I actually have several more variables, like Race, etc. but am omitting them here for simplicity.)
For example, in Alabama in 2006 50 male prisoners were Paroled during their 1st yr (0-1). 5 died during their 1st yr, etc. Notice that no males died during the 2-4 year interval, so there isn't a row for that. Also, for 2007 (not shown), there won't be rows for the 5-6 interval because it hasn't finished yet (need to wait for 2013 for that data).
I can pivot the data like so, using the Sum of Count for the cells:
But I'd also like to calculate these summary statistics, flexibly enough so that if I restructure the pivot table the statistics get recalculated.
1. The number entering each interval.
- For the first (0-1) interval, this is 121 or (70+6+25+2+14+4) (i.e., the Grand Total).
- For the subsequent intervals, it's the number of prisoners remaining in prison at the end of the previous interval. For example, the number entering the 2-4 interval = 45 which is 121-(70+6). The number entering the 5-6 interval = 18 which is 45-(25+2).
2. The % paroled per interval.
- The number paroled during that interval / the number entering that interval
3. The cumulative % paroled, by interval.
- The cumulative number paroled / the number entering in the first year
I've worked at this for a while and can't get these statistics (I tried calculated fields but couldn't figure out the formulas). I'm also willing to use helper columns, but they quickly fell short because I may need to show the data by certain States, certain Year(s), certain age groups, etc.
Any suggestions? Thank you.
Bookmarks