Data Analysis Tool For All

Eligible population data from the U.S. Census American Community Survey at Census Bureau Data:

• S1701 Poverty Status in the Past 12 Months
  • Contents: Poverty (<100%) counts and percentages by age, sex, race, educational attainment, employment status, and work experience, and population totals by poverty at various levels (including <125%, <200%, etc.).
  • Data Limitation: This table does not provide detailed demographic characteristics about people at poverty levels other than the <100% poverty level population, but it does have data for all 5 counties, including counties with small populations.

Intake data from your case management system:

• Fields:
  • Demographics about which you are curious, such as Age at Intake, Gender, Race, Ethnicity, With Disabilities, Veteran, Percentage of Poverty, Number in Household, Citizenship Status, Language, Living Arrangement, County, etc.
  • Dates: Date of Earliest, Intake Date, Prescreen Date, Open Date, Close Date, Date of Rejection, and Date of Birth (for use in an age-related formula).
  • Case Information: Legal Problem Code, Problem Code Categories, Close Reason, Rejection Reason, Disposition, Intake Type, etc.
• Filters/Report Structure:
  • Date Filters: Date of Earliest in last year, or Intake Date or Prescreen Date in last year.
    Exclude/Filter Out: Test or fake cases and clients and duplicate cases using whichever fields your organizations uses to identify these cases and clients, such as Rejection Reason, Client Name, Case Status, Funding Code, etc.
  • Include: Cases that were closed with service, rejected without service, or remain open.
  • One Row/Record Per Case: Ensure that downloaded data includes just one record (one row) per case. If necessary, text join fields that cause more than one row/record per case.
• Export Format:

  • If downloading from LegalServer, it is recommended that report results are exported as CSV files (an option that may be set up under Additional Display Format – remember to check Headers in First Row) and saved as Excel files to avoid formatting issues, particularly related to date fields.

    

    
     

Part 1: Collecting External U.S. Census Data:

1. Open the Census Bureau's Explore Census Data website.
2. Type "s1701" in the search bar for our example.
   1. In the future, if you know you need the S1701 Poverty Status in the Past 12 Months table, type "s1701" in the search bar. If you do not know the table name, you may enter a search term, such as "poverty" to be taken to a list of relevant tables, including S1701.
3. Click on the S1701 table.
   
4. Click on the S1701 table.
5. Click on +View All 25 Products under the table name.
6. Click on 2023: ACS 5-Year Estimates Subject Tables.
   1. If your geographies have populations over 65,000 you may stay with the default 1-Year Estimates, which provide more current, but less reliable data or you may use 5-Year Estimates, which provide less current, but more reliable 5-year averages. If some or all of your geographies have fewer than 65,000 people (like La Paz County, AZ in this example), you must select 5-Year Estimates.
   2. For information about choosing 5-year or 1-year estimates, refer to Using 1-Year or 5-Year American Community Survey Data.
      
7. Under Geographies in the far-left filter panel, select a geographic level, and click on it (In this example: geographic level is County).
8. Select a state (Arizona in this example).
9. Click on all the counties you need added (5 Arizona counties were selected in this example: La Paz, Maricopa, Mohave, Yavapai, & Yuma).
   
10. Once all geographies have been added, click on close panel << button.
11. Verify that 5-Year Estimates are still indicated under the table name in the top left and if instead 1-Year Estimates are indicated, click on the Dataset button and reselect 2023 5-Year Estimates (to ensure that La Paz county data are included in this example).
12. To avoid downloading the Margin of Error data, click on the Margin of Error button along the top ribbon before downloading the data.
    
13. Click on the Excel button along the table ribbon to download an Excel version of the table or the CSV button to download a CSV version of the table. If you do not see the Excel or CSV buttons in the top ribbon, click on More Tools at the far right of the ribbon.
    1. The Excel version is formatted to be more user-friendly and includes an Information tab that provides table details and notes whereas the CSV version includes only unformatted data.
    2. The CSV version can be easier to work with when you are downloading data for more than one geography as long as they are saved as Excel files after being downloaded in CSV format.
14. Open the data file:
    1. If the numbers downloaded in text format, highlight the relevant cells, right click, and select Convert to Number.
    2. In the Calculation steps below, focus on the top row, Population for whom poverty status is determined (which is the total poverty and non-poverty population combined) and the 200 percent of the poverty level row.
       

Part 2: Collecting Internal Case Management System Data:

1. Download data from your case management system (CMS), use an API link to connect to data in your CMS, or connect to your CMS data directly via an ODBC connection or other method.
   1. Make sure to include intakes that remain open, that were rejected, and that were closed (either in the year they were opened or later).
2. Create PivotTable
   1. Click Insert along the top ribbon, then click on PivotTable.
   2. You will see a blank PivotTable.
   3. Sort the fields in the PivotTable Fields window, by clicking on the Gear Icon, and selecting Sort A to Z.
       
   4. Add the Matter/Case ID (or Case Number) field to the Values window and make sure the calculation is Count.
      1. If you see Sum of Matter/Case ID, click on the drop down, select Value Field Settings, and change the calculation type to Count.
         
   5. Drag County of Residence into the Rows window.
      1. Assuming you do not need to map intakes from counties outside of your service area, you may click on the Row Labels drop-down in the PivotTable and uncheck all but the service area counties.
         

Part 3: Combining External & Internal Data:

1. Perform calculations:
   1. Copy the county names (making sure to include the word “County” followed by a comma and the name of the state, which is important for the mapping steps below) into a new calculation table to the right of the downloaded data.
   2. Copy the Less than 200 percent of poverty level numbers from the U.S. Census data previously downloaded for each county into the new table.
   3. Calculate each county’s Share of Intakes and Share of the <200% Poverty Population by dividing each county's number by the service area's total number.
   4. Calculate each county’s Location Quotient (LQ) by dividing the Share of Intakes by its Share of the <200% Poverty Population.
      1. Note that LQs in the range of 0.75-1.25 are generally considered in the normal/expected range, thus in this example, all counties in the service area are within the normal range, except for La Paz, from which a slightly higher than expected share of intakes were received in 2023, but its LQ is still very close to the normal/expected range.
         
2. Create map:
   1. Highlight the County column and the Location Quotient column and click on Insert-Maps-Filled Maps.
   2. Right-click on the map and select Format Data Series
      1. Leave Map projection Automatic but consider changing Map area to Only regions with data. Change Map labels to Show all.
      2. Change Series Color to Diverging (3-color), varying the colors more significantly when most LQs are below 0.75 or above 1.25 and less significantly when most LQs are in the normal range.
      3. With the chart selected, click on Chart Elements (green Plus Sign in the top right corner) and click on the check box next to Data Labels. County LQs will appear.
      4. Edit the Title: Click on the existing title and type over it. You may change the font size and make it bold.
      5. Add a note explaining whether the LQs outside of or within the normal/expected range.