Data Questions
Who is eligible?
Who requests assistance?
Who do we help?
How do we help?
What resources are required?
Geographic Concentration
Does the geographic concentration of served intakes match the geographic concentration of organizational hours?
Geographic Concentration
Geographic Concentration analyses compare geographic concentrations (high or low) of multiple variables to determine how the variables and location impact each other. Our concentration maps display location quotients.
Example Data Question
How does the proportion of hours we spend working for clients from different parts of our service area compare to the proportion of eligible people in those same areas?
Multiple Analyses Are Possible
- Identifying concentration allows you to move beyond questions of “high/low” to questions of “more than expected or less than expected”.
- The location quotient is a commonly used measure of concentration, and it can yield important insights into the data.
- Are there communities in your service area generating fewer than expected work hours (LQ <1)? Why might that be?
- Are there communities in your service area generating more than expected work hours (LQ>1)? Do you know why?
- Are levels of concentration throughout your service area (the distribution of LQs on a map) the result of a conscious effort on the part of your organization, or is it simply determined by the population seeking your services?
Data Sources
Intake and Closed Case data from your case management system, including:
- Fields that show hours worked on cases, including the date of the hours worked
- Open date and close date
- County (or other geographic field such as zip code)
- Other case information that you might be able to use in other analyses, demographics, etc.
- Exclude cases that were identified as errors or duplicates and cases that ended up not being served
U.S. Census American Community Survey data at the American Factfinder website: http://factfinder2.census.gov (Advanced Search option)
- S1701: POVERTY STATUS IN THE PAST 12 MONTHS, 5 year estimate, by Census Tracts
Example Analyses Steps
- Export the total hours worked from your CMS to a spreadsheet for the most recently completed year or the most recent year for which the ACS S1701 table is available.
- Sort the hours by county. Review the counties and remove any that aren’t actual county names or aren’t in your service area. You may have to combine data if counties show up with multiple spellings.
- Subtotal all hours worked. Then, calculate the percentage of hours worked in each county.
- Open the S1701 table and calculate the total poverty population for the state by adding up the Below Poverty Level Estimate column for each county. Then calculate the share of the total poverty population for each county. Add these percentages to a new column in your hours worked spreadsheet.
- In a new column called Concentration, calculate the location quotient by dividing the hours worked % for each county by the % share of the poverty population and divide that amount by 100. Results that are below 0.75 indicate that fewer hours were worked than would be expected in that county based on its share of the state’s poverty population. Results that are between 0.75-1.25 indicate that the expected share of hours worked matches that county’s share of the state’s poverty population. Results that are above 1.25 indicate that more hours were worked than would be expected in that county based on its share of the state’s poverty population.
- Create a column called Concentration Ranges in which you enter these categories: “0.01-0.74”, “0.75-1.25”, “1.25-3.00”, and “Less than 50 hours” (enter a threshold number of hours under which you will not display the concentration data).
- You should have a spreadsheet that simply shows County, Total Hours, Concentration, and Concentration Ranges.
- Login to Microsoft Power BI (create an account if you don’t already have one).
- Click on Get Data, then Excel, find the spreadsheet you just created, and click Open. Note that your spreadsheet will need to be in Microsoft Excel Worksheet format for Microsoft Power BI to import it into your document.
- Double click on the name of the sheet in your spreadsheet and then click Load.
- Insert a Filled Map Visualization.
- Enter County as Location and Concentration Ranges as Legend.
- Adjust the formatting as you prefer to show the variation in Concentration Ranges by county. Make the counties with Fewer than 50 Hours shaded white.
- Use the automatic Legend or create your own using shapes with titles.
- In order to include the map in other documents, you will have to take screen shots.
