Move Logic from R Scripts into Database Fields

[theryankelly]

As we explored and documented in our FY 21 work, there are several fields that are calculated in the creation of the report. To help keep data consistent, we will use SQL to generate this logic and store the fields in the database. For example, the count of students by grade that take family vehicles in the morning is a field calculated in the report logic {carTripPct}.

Priority Queries run by AECOM and partners that should drive the prioritization of the calculations below: // 1. Average distance (mi) by survey ID // 2. Number of Responses, Number of Students, & Average Distance by Survey ID (requires Excel aggregation) // 3. # of students in walkshed and bike shed by survey ID // 4. Transportation breakdown to/from school, by survey ID

Fields Calculated In Report

• [x] School Name: {School_Name}
• [ ] Date Report Generated: \today \\begin{itemize}
• [x] Dates Survey Range:{start_date} to {end_date} also need to create {start_month} {start_year}
• [x] Reponses Recieved (survey or students?): {sampleSize}
• [ ] School-wide Particiaption Rate (students/enrollment?): {surveyPct}
• [ ] School Enrollment Date: {enrollmentDate}
• [ ] School Enrollment Count by Grade:
• [ ] Students by grade % completed (students, grade/ enrollment): {surveyPct}
• [x] Average Distance to School: {avgDistToSchool}
• [ ] Within 1.0 Mile Walkshed: {pctWithinOneMile}
• [ ] Within 2.0 Mile Bikeshed: {pctWithinTwoMile}
• [x] Students By Walkshed Estimated (cat: 0.5 1.0 1.5 2.0 2.0+):{Students By Walkshed}
• [x] Students By Walkshed Surveyed (cat: 0.5 1.0 1.5 2.0 2.0+)
• [ ] Walk\Bike Trips Within One Mile: {pctWalkSchoolOneMile}
• [ ] Walk\Bike Trips Overall: {walkTripPct}
• [ ] Family Vehicle\Carpool Trips Overall: {carTripPct}
• [ ] School Bus Trips Beyond One Mile: {pctSchoolBusBeyondOneMile}
• [ ] Morning Mode Choices (cat:types / total): {walkTripMorningPct}
• [ ] Afternoon Mode Choices (cat:types / total): {walkTripAfternoonPct}
• [ ] Walk share Morning
• [ ] Walk share Afternoon
• [ ] Auto share Morning / Auto share Afternoon
• [ ] Travel Mode, Morning (cat: 0.5 1.0 1.5 2.0 2.0+)
• [ ] Travel Mode, Afternoon (cat: 0.5 1.0 1.5 2.0 2.0+)
• [ ] Per-student GHGs within 1 mile: {round(ghgPerCap10Buffer,0)}
• [ ] Per-student GHGs beyond 1 mile: {round(ghgPerCap10PlusBuffer,0}
• [ ] Actual Walk\Bike Share (cat: 0.5 1.0 1.5 2.0 2.0+)
• [ ] Expected Walk\Bike Share (cat: 0.5 1.0 1.5 2.0 2.0+)
• [ ] Taregt reduction of number of daily car trips: {dailyAutoReduce}
• [ ] Min of additional physical activity for each newly participating student: avgMinutesWalking
• [ ] Reduce annual auto-generated GHG emissions from between # and %: {annualGHGreductionLowText}

[theryankelly]

• [ ] Will first need to identify what calculated fields are needed in priority report generation.
• [ ] Then need to identify what calculated fields are used in Tableau visualizations
• [ ] Then need to document the remaining "report only calculations" for a decision on replication

[theryankelly]

Following up on our call yesterday. Looking back at the prior demo Tableau work we used dates in a few places: Once to get a count of students per year. Shown here we originally interpreted it as the Calendar year. This could also be aligned with the Fiscal Year the date we use when calculating enrollment.

We also ran into conflicts when looking at month-by-month totals. As some surveys are conducted across months. Looking at one month may underrepresent the responses for a given survey that is split.

I will also mention the morning vs afternoon split is based on to and from school binary, not a date/time calculation.

[TarinAComer]

Enrollment data is in postgres now. Scripts from Sharepoint have been set up as Views Above views have all been combined into a full View.

Next steps when given approval are to import the above fields into the a full survey view.