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, 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, 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.
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. Age Ranges: =IF(AND(AI2=0,ISBLANK(AH2)),"Unknown Age", IF(AND(AI2=0,NOT(ISBLANK(AH2))),"0-17", IF(AI2<=17,"0-17", IF(AI2<=34,"18-34", IF(AI2<=64,"35-64","65+"))))).
      1. Note that AI2=Age at Intake & AH3=Date of Birth.
      2. Because some databases enter Age=0 when the Date of Birth is blank, this formula ensures that you do not overcount newborns.
   3. 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.
   4. Intake Pathway: =IF(O2<>"","Rejected", 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")))))
      1. Note that O2=Rejection Reason & N2=Close Reason.
      2. This formula divides cases up into Rejected, Remaining Open, Advice/Limited, and Extended categories.
         
3. Create Pivot Charts
   1. Click Insert along the top ribbon, then click on PivotChart.
      1. Place the first PivotChart in a New Worksheet and rename it Charts.
      2. Place additional PivotCharts in the same Charts worksheet.
         
   2. You will see a blank PivotTable and a blank PivotChart.
      
   3. Sort the fields in the PivotChart 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 any of the fields you are interested in analyzing into the Axis (Categories) window and notice how the PivotTable and PivotChart update
      
   6. Format the chart as you prefer. Here are the steps taken to change the original chart to this final version:
      
      1. Change the color palette: With the chart selected, click on Design along the top ribbon and then click on Change Colors.
      2. Change the Chart Type: With the chart selected, click on Design along the top ribbon and then click on Change Chart Type (in this example, Pie Chart was selected).
      3. Edit the Title: Click on the existing title and type over it. You may change the font size and make it bold.
      4. Remove PivotChart Buttons: If you will be sending the entire spreadsheet to a colleague and want them to be able to filter the visual themselves, you may want to leave the PivotChart Axis Field Button, but you will likely still want to remove the Value Field Button. If you will be copying this visual into a presentation or sending a static copy to a colleague for their viewing only, you will likely want to remove all PivotChart buttons. To remove some or all buttons, with the chart selected, click on PivtoChart Analyze along the top ribbon, click on the Field Buttons drop down, and uncheck specific buttons or select Hide All to remove all buttons.
         
      5. Hide Unknown or Blank Data: Click on the filter button and unchecking Unknown or Blank in the PivotTable column header called Row Labels. There may be significant blank data when working with intakes and prescreens. You may want to leave the blank data in your charts, but hiding it provides the viewers insight into the share of intakes/prescreens by demographic characteristic among those for whom that characteristic is known. These known data percentages can serve as a good representation, or proxy estimate, for all those who requested assistance.
      6. Other Formatting Options:
         1. Especially when there are many categories (like Legal Problem Categories), right click in the PivotTable and Sort Largest to Smallest. This will make your charts easier to read, unless you prefer showing the categories in the numerical or alphabetical order.
         2. Under Chart Elements, remove or move the Legend.
         3. Add Data Labels, and Under Format Data Labels edit them to show Percentage and Category Name, change the Number Category, and adjust the number of decimals places that appear.
         4. Under Format Data Series, consider changing the angle of the first slice.
         5. Because you may want to show numbers rather than percentages in some charts, highlight the numbers in the PivotTables and click on the Comma button to change the number format to show thousand separators, but make sure to reduce the decimal places to zero.
         6. After adding more PivotCharts to your Charts tab, format all charts to be the same size, with the same fonts, labeling styles, etc.
            
4. Refreshing Data: Once a spreadsheet like is set up, you may download new data and copy it into data tab from which the Chart tab pulls data.
   1. Make sure the columns of the new data match the columns in the existing spreadsheet exactly.
   2. If there are more records in the new data, the formulas created in the far-right columns will need to be copied down to all rows.
   3. From within your Charts tab, select one of your PivotCharts or PivotTables, then click on PivotChart Analyze along the top ribbon. First click on Change Data Source and make sure that all new rows of data are included. Then click on Refresh and Refresh All. Every PivotTable and PivotChart should update.