9 Replies Latest reply on Dec 19, 2017 1:15 PM by Joe Oppelt

    Calculating attendance rates with only absence information

    Brian Goddard

      So the problem I'm working with in theory feels like it should be very simple, but for some reason I can't get my head around it. I'm trying to calculate student attendance rates using their entry date and their day by day absences (I can't have row by row attendance, because students aren't marked present, they're only marked absent or tardy).

       

      My idea is to sum each student's absences and divide by the number of school days they've been enrolled. Total absences is simple enough:

           Daily absence is just .1666 absences per period absent

           Total absences is then {FIXED [Internal ID]: SUM([Daily Absence])}

       

      Now comes the tricky part. The number of school days a student has been enrolled should be essentially COUNTD([Date]>=[Entry Date]) i.e. the number of dates in the file that are greater than or equal to the student's first day. My problem is that this has varying levels of granularity that is just sort of messing with me. Each entry date is fixed to the student level, but the Dates that need to be counted are on the global level. Whenever I try to calculate Days Possible, I end up with the total number of days in the school year, for example, I've tried:

           {FIXED :COUNTD(IF [Date]>={ INCLUDE [Internal ID]:MIN(Entry Date)} THEN [Date] END)}, but it spits out the same number for each student.

       

      I've attached a packaged workbook with the data structure and what I've tried so far. If anyone has any ideas about how to make this work I'd really appreciate it.