Data Analysis Tool For All

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, 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: Intake 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, and 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.
   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 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 Served or Not formula into the Columns Window.

      

   6. Copy PivotTable data (values only) into a calculation table to the right of the data, change “Row Labels” to “Zip Code”.

      

3. Perform Calculations:
   1. Add columns to calculate each Zip Code’s Share of Served Intakes and Share of Total Intakes by dividing each county's numbers by the total column numbers.
   2. Calculate each county’s Location Quotient (LQ) by dividing the Share of Served Intakes and Share of Total Intakes.

      1. 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.

         

4. 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 outside of or within the normal/expected range.