Attendance Mapping | Powerschool

We need to map fields used in Powerschool to our Rethink Platform. The chart below shows items used to make calculations (some the user won’t see) and also Mapping Points which the user will see and need to be part of the design. From a Data Analytics perspective, the first goal is to create calculations used to identify which Tier is recommended for the student in Attendance and Behavior. The current plan for attendance and behavior is to show the tier for each student for Tardies, absences, and incidents.

SIS Data Dictionary: PowerSchool SIS Views (powerschool-docs.com)

Mapping .TXT files

PM_Attendance_daily_export.txt

student_school_enrollment.txt

Note that student_school_enrollment.txt isn’t strictly necessary for the MVP for the 2023-2024 back-to-school period, but we plan to make use of it in the future.

See https://rethinkautism.atlassian.net/l/cp/Srp0UmLR

Attendance: PM_Attendance_Daily_eport.txt and Student_school_enrollment.txt fileMapping Logic:

PS Field needed	Rethink Field Mapping Points	Logic for Rethink Set Up
ATTENDANCE.STUDENT_NUMBER Values ( 5-6 digit unique student identifier) PM_Attendance_daily_export.txt.	Student ID- MTSS Dashboard Student View Open	For Belleville, this is the student identifier column
ATTENDANCE.ATT_DATE Values (Belleville: 4-digit year - 2 digit month-2-digit date example: 2022-12-20 ) PM_Attendance_daily_export.txt.	Date- MTSS dashboard Student View Open	Use to get the count of unique dates within the combination of ATTENDANCE STUDENT_NUMBER and ATTENDANCE ATTENDANCE_STATUS. (Note that for Belleville ATTENDANCE ATTENDANCE_STATUS signals whether the event was an absence or a tardy; 1 = tardy and 2 = absence.)
ATTENDANCE ATTENDANCE_STATUS Values (1 or 2) PM_Attendance_daily_export.txt.	Tardy/Absence, Total absences, total tardies-Student View	Signals whether the event was an absence or a tardy (1 = tardy and 2 = absence).
STUDENTS ENTRYDATE Values (Belleville: 4-digit year - 2 digit month-2-digit date example: 2022-12-20 ) student_school_enrollment.txt file	Used in calculation for absence rate and tardy rate.	Important note: We are planning on requiring customers to enter the school year start date and the school year end date in a form when they first start using the product. So we intend to use those user-supplied dates as the “source of truth” for school year start date and school year end date, not the information supplied by STUDENTS ENTRYDATE field. In case we do not have school year start date from the intake form as described in the above bullet point, an alternate method could be to find the earliest date from the student_school_enrollment.txt file, STUDENTS ENTRYDATE column. Subtract the earliest date found above from the date of the data export to get the number of calendar days that have passed so far in the school year.
SchoolYearStartDate Values (Belleville datetime field with 4-digit year, 2-digit month, and 2-digit day plus hours, minutes, seconds, milliseconds; example: 2022-08-29 00:00:00.000) SR.csv, SM.csv, SEL.csv		Important note: We are planning on requiring customers to enter the school year start date and the school year end date in a form when they first start using the product. So we intend to use those user-supplied dates as the “source of truth” for school year start date and school year end date, not the information supplied by STUDENTS ENTRYDATE field. In case we do not have school year start date from the intake form as described in the above bullet point, an alternate method could be to use the SchoolYearStartDate from Renaissance data files (SM.csv, SEL.csv, or SR.csv). Subtract the earliest date of SchoolYearStartDate from the date of the data export to get the number of calendar days that have passed so far in the school year.

Additional Fields Needed --10-12-23

Attendance Calculation Rules

Important note: None of the calculations or recommended tiers below will be shown until 4 weeks (28 days) past the start of the academic year. Districts that only record absences and/or tardies, but not days/classes when students are present will require the product to show 0 absences and/or tardies for any student missing from the data (which will be any student with no recorded absences or tardies), and therefore no students in these districts will have missing data for attendance or for attendance tiers. This is the case for Belleville. However, we expect some districts we integrate in the future to record days/classes when students were absent and/or tardy as well as when they were present. In those cases, students missing from the district’s attendance data will have missing data for attendance and for attendance tiers (because these students were never recorded as absent/tardy and were also never recorded as present). We recommend allowing for a missing data category when showing attendance tiers to allow for both types of attendance data (attendance data only recording absences/tardies and attendance data recording when students were present in addition to absences/tardies).

Generally, we expect that customers will give us an attendance data file that includes fields/columns that give a student identifier, the date of the attendance event, and the type of attendance event (whether the attendance event was a tardy or absence). Important note: We are planning on requiring customers to enter the school year start date and the school year end date in a form when they first start using the product. So we intend to use those user-supplied dates as the “source of truth” for school year start date and school year end date. For Belleville, the attendance data is in the file named PM_Attendance_daily_export.txt. At the time this information was updated (07/14/2023), we also make use of one column (SchoolYearStartDate) from the SM.csv file until we can use the school year start date from the required form. For Belleville, and the columns used in the Belleville attendance data to determine flagging for tiers are ATTENDANCE STUDENT_NUMBER, ATTENDANCE ATT_DATE, and ATTENDANCE ATTENDANCE_STATUS.

1. First, you will need to calculate an absence rate and a tardy rate for each student. You do that by:

   1. Getting a count of the number of unique dates from the column giving the date of each attendance event by student identifier and the type of attendance event (tardy or absence). At the end of this step, you will have a count of the number of dates with one or more tardy or absence for each student. Note that for any student who has 1+ tardy and 1+ absence in the data, we expect to have two separate counts: one for tardies, and one for absences. Additionally, it is important to count the number of unique dates for each type of attendance event (absence or tardy), not the number each type of event overall. For Belleville, you will use the column ATTENDANCE ATT_DATE to get the count of unique dates within the combination of ATTENDANCE STUDENT_NUMBER and ATTENDANCE ATTENDANCE_STATUS. (Note that for Belleville ATTENDANCE ATTENDANCE_STATUS signals whether the event was an absence or a tardy; 1 = tardy and 2 = absence.)

   2. Getting the number of calendar days that have passed since the beginning of the year until the date of the data export. To do this:

      1. Find the school year start date. For Belleville, you will use the earliest SchoolYearStartDate column from the SM.csv file; for Belleville’s data, the value is the same for all rows in the file, so you can simply take the value from the first row, but we specify here to use the earliest date in case other districts have more than one date in this column from Renaissance data.

      2. Subtract the school year start date found above from the date of the data export to get the number of calendar days that have passed so far in the school year.

      3. Again, you will only be showing the rate for tardies and absences if 4 weeks (28 days) have passed since the beginning of the school year. You will also only be showing the recommended tiers if 4 weeks (28 days) have passed since the beginning of the school year.

2. Within each combination of student identifier and the type of attendance event, divide the count of unique dates found in step a by the number of calendar days passed in step b and then multiply the result by 160, rounding the result to the nearest whole number. For Belleville, the columns giving the student identifier and the type of attendance event are ATTENDANCE STUDENT_NUMBER and ATTENDANCE ATTENDANCE_STATUS.

   1. Why multiply the result by 160?

   2. Answer: Tara calculated that there are approximately 1.6 calendar days for every school day. Multiplying by a factor of 1.6 is necessary to get an accurate incident rate that reflects the estimated number of school days instead of the number of calendar days.

   3. Additionally, we want the decimal rate to be converted to a 0-100 percentage. Thus we multiply by 1.6 * 100 to get an accurate rate for school days that is presented as a 0-100 percentage.

3. Since the above is an approximation of the percentage of school days with absences and tardies, it is possible to have a number over 100 (erroneously signaling that a student has been absent or tardy over 100% of school days). Replace any value greater than 100 with 100 to keep the maximum possible value within the expected bounds for an absence and tardy rate. Field/column requirements for tardy rate and attendance rate:

   1. Minimum: 0

   2. Maximum: 100

   3. Length (number of digits): 3

4. Once all of the steps are completed, any student with a recorded absence event should have a whole number absence rate ranging from 0-100, and any student with a recorded tardy event should have a whole number tardy rate ranging from 0-100 (see note for step 2 about rounding). Important note: For Belleville and likely many other districts, students missing from the attendance data should be assigned absence and tardy rates of 0. (They are only in the data if they have an absence or tardy event, so not being in the data suggests absence and tardy rates of 0 for those students. Additionally, if students have no recorded tardy events in the data, they should be assigned a tardy rate of 0, and if students have no recorded absence events in the data, they should be assigned an absence rate of 0. It is possible for the student to be in the data for one type of event but not the other, so it is important to assign rates of 0 appropriately when a student does not have recorded tardies or absences.) Some districts may record days/classes when students were absent, tardy, and/or present, so how to handle students with no recorded attendance events may need to be adjusted as we integrate more customers. Again, for Belleville, ATTENDANCE ATTENDANCE_STATUS signals whether the event was an absence or a tardy; 1 = tardy and 2 = absence.

Here is the default mapping, which should be done separately for each student’s attendance vs. tardy rate (i.e., a student can be in different tiers for attendance rate vs. tardy rate; see note below about at risk and approaching indicators when the user changes the default):

• Rate >= 15 = tier 3

• Rate >= 10 and < 15 = tier 2

  • Rate = 13 or 14 = tier 2 with an at risk indicator (such as a down arrow)

• Rate <10 = tier 1

  • Rate = 8 or 9 = tier 1 with an at risk indicator (such as an up arrow)

• Important: When a user changes from the default mapping for tiers, the at risk indicator (such as a down arrow) will apply when the student has the maximum rate or the maximum rate minus 1 percentage point for tier 1 or tier 2. There is no “approaching” indicator for attendance. This is true for both absences and tardies.

1. Note: I can share R code I wrote to compute absence and tardy rates if helpful.

2. Also note: Roman has pointed out that having an absence or tardy on the first day will result in a rate of 100%, which could be confusing. I (Tara) have incorporated a wait time of 4 weeks (28 days) before showing attendance or tardy rates or recommended tiers.