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

# Calculating attendance rates with only absence information

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.

• ###### 1. Re: Calculating attendance rates with only absence information

I haven't looked at the workbook yet, but if you want the count of dates by student, then you want to change this:

{FIXED :COUNTD(IF [Date]>={ INCLUDE [Internal ID]:MIN(Entry Date)} THEN [Date] END)}

... to this:

{FIXED [student ID]  :COUNTD(IF [Date]>={ INCLUDE [Internal ID]:MIN(Entry Date)} THEN [Date] END)}

• ###### 2. Re: Calculating attendance rates with only absence information

Also, if you have varying enrollment dates by student, I think the DATEDIFF in days between enrollment date and TODAY() would give you the number of enrollment days.

• ###### 3. Re: Calculating attendance rates with only absence information

Hi Brian

see the attached

I added a couple of calculations

first to fix the entry date for each student

Then to determine the date difference as

Then the final calculation

It will return this

I am concerned about the date diff calculation because it will include the weekends - holidays etc - but I did not see a School calendar" to use

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 4. Re: Calculating attendance rates with only absence information

See attached.  I'm not sure what's going on in your original calc, but I made a copy and did it my way.  See what you think...

• ###### 5. Re: Calculating attendance rates with only absence information

The reason I was using COUNTD as opposed to DATEDIFF is because

1) DATEDIFF counts every single day, including weekends and holidays. There have only been 72 school days thus far in the year, but the majority of students in this formulation have more than 100 days possible.

2) Any Student who enrolled in a previous year shouldn't be counted as more than the number of days since the start of the year. Note that your formulation has many students with well over 112 days possible, which is before the first day of school.

By using some formulation of COUNTD, I was only counting the days were there was some type of attendance marked (it is reasonable to assume that there will be at least one student with one tardy or absence every single day, so I should be able to count those days).

• ###### 6. Re: Calculating attendance rates with only absence information

Brian -- The weekend implications explain something Jim was asking.

So in the workbook I previously uploaded I added the student's ID into your original calc, and that comes up with 70 as the largest [Days Possible] value.

• ###### 7. Re: Calculating attendance rates with only absence information

This solution isn't actually looking at the total possible days, but the days between the entry date and the most recent day the student had either a tardy or an absence (note that students without any attendance records have this field as null).

Perhaps I should have started by explaining the data format a bit more. There is one row per student per date that a student has an attendance record (either an absence or a tardy). There is also one row per student who have no attendance records, which will have the Date field blank. Every row has the student's entry date. We don't have a row for a student on a date that they were present for the whole day. If we had that, we could simply count the number of rows per student and subtract the total number of absences.

That said, you said something that intrigues me; you imply that if there were a school calendar you could use DATEDIFF to count the days between Entry Date and the most recent school day (i.e. {FIXED : Max(Date)}). With the assumption that every school day there will be at least one student absent or tardy, could the collection of dates in the data be treated as the calendar? And if so, how could you use DATEDIFF to return the appropriate value?

• ###### 8. Re: Calculating attendance rates with only absence information

DATEDIFF is just going to do calendar math and give you a number of calendar days.

But if you have a school calendar table, the [Number of Records] between date-A and date-B will give you your number.

• ###### 9. Re: Calculating attendance rates with only absence information

Wait. I misunderstood.  You were asking about the whole set of dates across all students in the existing data.  And unless there was a day everybody was in, you'd have all the school days represented.  Gotta think about that.  And Jim might already have something in mind for that.