Data Analysis Tool For All

Closed case 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, State, Zip Code, 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: Close Date in the 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.

    

    
     

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.
2. Create Formulas in the downloaded data (using Excel in this example).
   1. The following formulas create categories that are helpful for analysis.
      1. Copy these formulas into cells in the far-right columns of your downloaded data (make sure to update the formula references so that they are pointing to the correct fields in your data).
      2. Add these formulas to all rows of data before creating pivot charts.
   2. Poverty Ranges: =IF(AND(AE2=0,ISBLANK(Z2)),"Unknown", IF(AND(AE2=0,NOT(ISBLANK(Z2))),"a. Below 100% FPL", IF(AE2<100,"a. Below 100% FPL", IF(AE2<125,"b. Below 125% FPL", IF(AE2<200,"c. Below 200% FPL", "d. 200%+ FPL")))))
      1. Note that AE2=Percentage of Poverty & Z2=Types of Income.
      2. Because some databases enter Poverty=0% when Income is blank, this formula ensures that you do not overcount those who actually have $0 income and 0% Poverty Level.
   3. Levels of Service =IF(O2<>"","Not Served", IF(ISBLANK(N2),"Remaining Open", IF(LEFT(N2,1)="A","Advice/Limited", IF(LEFT(N2,1)="B","Advice/Limited", IF(OR(LEFT(N2,1)="F",LEFT(N2,1)="G",LEFT(N2,1)="H",LEFT(N2,1)="I",LEFT(N2,1)="K",LEFT(N2,1)="L"),"Extended", IF(LEFT(N2,1)="X","Not Served"))))))
      1. Note that O2=Rejection Reason & N2=Close Reason.
      2. This formula divides cases up into Advice/Limited, Extended, Not Served, & Remaining Open categories.

3. Create Map

   1. Click Insert along the top ribbon, then click on PivotTable.
      1. Place the PivotTable in a New Worksheet and rename it Map.
   2. Sort the fields in the PivotTable Fields window, by clicking on the Gear Icon, and selecting Sort A to Z.
   3. 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.

   4. Drag the Zip Code field in the Rows window. Click on the drop-down in the Row Labels cell of the PivotTable, click on Label Filters, select Between, and enter the range of Zip Codes in your service area in the Label Filter window that pops up (specific zip code numbers are distorted in this visual to protect confidential information). 

      

   5. Drag the Levels of Service formula into the Columns window. Click on the drop-down in the Column Labels cell of the PivotTable and uncheck Not Served and (blank).
   6. Drag Problem Code Category into the Filters window. Then, click on the drop-down arrow in cell B2 next to Problem Code Category (All) to filter the data to 30-39 Family only. Click OK.
   7. Copy PivotTable data (values only) into a calculation table to the right of the data, change “Row Labels” to “Zip Code”.

    

4. Perform Calculations:
   1. Add columns to calculate each Zip Code’s Share of Extended Service-Family and Share of Total Closed Cases-Family by diving each zip code's numbers by each column's total numbers.
   2. Calculate each county’s Location Quotient (LQ) by dividing the Share of Extended by the Share of Total Closed.
   3. Note that LQs in the range of 0.75-1.25 are generally considered in the normal/expected range. LQs below 0.75 are lower than expected and above 1.25 are higher than expected.
5. Create map:
   1. Highlight the Zip Code column and the Location Quotient column in your calculation table and click on Insert-Maps-Filled Maps from the top ribbon.
   2. Change the Map’s title by simply clicking on “Chart Title” and typing your preferred title.
   3. Right-click on a shaded zip code in the map and select Format Data Series.
      1. Select Map projection (usually Automatic or Mercator) and consider changing Map area to Only regions with data. Leave Map labels set to None.
      2. Change the Series Color to Diverging (3-color) & Change the Minimum, Midpoint, and Maximum to Numbers and enter 0.75, 1, and 1.25. You may change any of the colors. Select colors that indicate less service than expected for numbers close to the Minimum, expected service for numbers close to the Midpoint, and more service than expected for numbers close to the Maximum.
      3. To add labels, click the green plus sign in the top right corner of the map and selecting Data Labels in the Chart Elements box. To edit the Data Labels, select More Data Label Options to be taken to the Format Data Labels window.
      4. Check Category Name and Value and change the Number formatting if needed (zip code labels (Category Name) are not added to this visual to protect confidential information).
      5. Highlight any data label or the Legend on the map and change the font from the font options across the top of the Home screen.
      6. Add a note explaining whether the LQs are outside of or within the normal/expected range.