Help with metrics related to an attendance app

[iaj8]

I am working with a non-profit that manages teachers in schools. I am building an AppSheet app to track attendance. The database has all the students across all the schools and grades and the app allows the teacher to input attendance for all the students in a given grade in a given school at a time. I now want to build a metrics view that allows the teacher to look at average attendance for a given period and also flag individual students with less than some threshold attendance in that period. I am able to write an expression (SELECT(Attendance Log[StudentName],
AND([School] = [Select School], [Class] = [Select Class], [Present], [Date] >= [Start Date], [Date] <= [End Date]))) to get all the days all the students in a class were present during a given period, but I am now unsure how to get the attendance percentage for each individual student since I cannot seem to incorporate loop logic into the expression. Am I approaching this the right way? Any help would be appreciated!

[Suvrutt_Gurjar]

Your requirement will be clearer if you share the table columns etc.

In general it sounds that you wish to have attendance metrics over a certain period of time. In AppSheet in general this will need to be achieved with multirow expressions ( the way you have started building with SELECT() ). Additionally if you want your metrics real time, meaning any change in attendance should reflect  immediately, those SELECT() expressions will generally need to be in virtual columns. However such metrics just for showing statistics with several SELECT() statments may put a drag on app's sync time.

Instead you may wish to evaluate charting options of AppSheet. Again charting options are limited in AppSheet.

Another option is to use Group by  and Aggregate by options in summary views.

Both charting and group by options will typically not put a drag on app sync time.

Finally if you are really looking for elaborate metrics, you could use options like Google Data Studio to plot data from an AppSheet app back end source.

https://help.appsheet.com/en/articles/961557-charts-the-essentials

https://help.appsheet.com/en/articles/961518-how-to-control-grouping

https://help.appsheet.com/en/articles/962230-improving-the-speed-of-sync#6-make-sync-happen-in-the-b...

[iaj8]

Thanks for your response!

Here are my relevant tables.

Attendance Log which contains a row for each time a student is marked as Present/Absent. This row contains a "Date" column:

Attendance Log Stats which is essentially an abstraction table that allows me to create the view in the app that will eventually show average attendance and students with poor attendance:

My goal is to use a view like this where you select the School, Class, Start & End Dates and then get the average attendance over all students in that class over that period as well as a list of the students with less than say 40% attendance:

[Suvrutt_Gurjar]

Thank you. For the latter part of showing statistics based on user selection, please explore the below sample app. That app is for single user selection. You will need a users table , if you wish to give that selection option to multiple users.

https://www.appsheet.com/templates/Allow-the-user-to-filter-a-view-based-on-a-form?appGuidString=912...

[iaj8]

Thank you for this. I have a users (students) table. I'll take a look at the options using Group By and Aggregates and the functionality in the app you have just shared and see if I can come up with a solution to my problem. Thanks so much!

[iaj8]

Thanks for the example app. In my current set up, the view allows someone to select a class and school and then selects all the attendance records related to all students in this class in this school. What I am left with is a List (called Present Students) containing attendance records. When I try to represent this list (which is a column in the Attendance Log Stats table) as a chart however, instead of treating each element of the list as a separate data point on the chart (which is what I want), it treats the entire list as a single data point, like so:

If I make a chart directly on the Attendance Log table instead of on the Present Students column of the Attendance Log Stats table it looks more like what I want, but then I am unable to restrict the data by date:

I know my questions aren't super clear but I'm new to the platform and still trying to figure out the exact vocabulary of what I'm trying to convey, sorry about that! I do feel that what I want to do is possible with AppSheet however.

[Suvrutt_Gurjar]

Thank you. It is not exactly clear what you mean restrict data by date. You have filters of "Start Date' and "End Date" correct? You could create slice to restrict date by those dates.

Also hope you are sharing test or sample data in posts. If not , please always share only test data and never real/confidential data in public forums.

[iaj8]

Thank you! Yes this is dummy data - but in future I will anonymize the names as well. Thanks for the suggestion.

Ok so creating a slice worked very well, thanks! I am now able to select a school and class, set the start and end dates and then see a graph of attendance of all the students during those dates.

But I am again stuck on where to go from here. This is what the graph looks like, but I also want to compute the following metrics:

- Average attendance this month (% of attendance each day averaged over the month)

- Names of students who were below 40% attendance overall this month

 Here is the expression I used to create my slice:

AND(
ANY(Attendance Log Stats[Select School]) = [School],
ANY(Attendance Log Stats[Select Class]) = [Class],
[Present],
ANY(Attendance Log Stats[Start Date]) <= [Date],
ANY(Attendance Log Stats[End Date]) >= [Date]
)

I think I probably need to add a virtual column to my slice table that computes aggregates over the rows in it, but I am confused where to start.

[Suvrutt_Gurjar]

Yes, since you wish to compute against monthly statistics, I believe you will first need aggregation, in the form of VCs.

[iaj8]

Cool, I'll try and figure out how to do this. But as a question at the outset: if I have a column of the List type, with the following values for example:

[Student 1, Student 1, Student 1, Student 2, Student 2] ie Student 1 is represented 3 times and Student 2 is represented twice, and I want to compute a result like this:

Student 1 - 3

Student 2 - 2

What function would work for this? At the moment I'm having trouble finding the correct expressions for List data structures.

[Suvrutt_Gurjar]

The best approach will depend on how you are creating the list in the app.

[iaj8]

As of now I create the list using the SELECT statement described above. Essentially I have the Attendance Log table with rows corresponding to each time a student is marked as Present or Absent. Then I have a wrapper table called Attendance Log Stats which just allows the user to select a school, class and date range, and then selects attendance records from the Attendance Log table using the following statement:

SELECT(Attendance Log[StudentID],
AND([School] = [Select School], [Class] = [Select Class], [Present], [Date] >= [Start Date], [Date] <= [End Date]))

The result of this statement is currently stored as a List column in the Attendance Log Stats table and this is the list over which I would want to compute the result described above.

If this is not the best way to create the List I definitely can change how I do that.

[iaj8]

Hi Mr Gurjar, sorry to bother you again with this, and thank you so so much for all your help, it has been crucial to the success of the app building thus far!

Based on how I construct the List as described above how do you suggest I compute those sorts of summary results? Or should I construct the List differently so that I can use a different set of functions to compute those results?

[Suvrutt_Gurjar]

For SELECT() based list, to create a list by a single element (Student Name in your case) you need to give input of Student Name to the SELECT() argument either from the Filter or using approaches such as [_THISROW]

https://help.appsheet.com/en/articles/2357308-filter

SELECT(Attendance Log[StudentID],
AND([School] = [Select School], [Class] = [Select Class], [Present], [Date] >= [Start Date], [Date] <= [End Date], [StudentID]=[Select Student]))

[iaj8]

Right I'm able to create the list by Student Name, but once I have the list, say for example:

[Student 1, Student 1, Student 1, Student 2, Student 2]

Then I want to get the count of each element in the list. I know how to do it for a given element using the COUNT function, but how do I do it for each element and get a result like this:

Count of "Student 1" - 3
Count of "Student 2" - 2

[Suvrutt_Gurjar]

Instead of creating a list of all students and then again segregating each student count, will it not be better to create a list of single student as suggested and then apply COUNT() function to that list?

[iaj8]

The issue with that is that then the user of the app will have to check each student individually for attendance being below the threshold. I want the app to automatically check the attendance of each student in the class and only display the names of those with less than the threshold attendance. I could try and do this with a graph of attendance that colours the bars corresponding to students with low attendance a different colour but even more preferable than this would be a virtual column that automatically computes the names of students with below threshold attendance and only stores these names.

[Suvrutt_Gurjar]

1) If you have a slice that first slices the data by school , class and date range, you could then simply apply group by to that data by student name to get a count of attendance.  

2)Or else you could have a VC in your attendance table with an expression

COUNT(SELECT(MonthlySlice[StudentID], [StudentID]=[_THISROW].[StudentID]))/[Monthly Count]

As mentioned earlier , multirow computation VCs put a drag on the sync time.

[iaj8]

Perfect I think this is the solution I'm after. Will try out and see how it  works. Thank you so so much once again!

[iaj8]

It's all working now! The slice of Attendance Log gives me the attendance logs only within the dates I'm interested in and then I added a count VC to the Students table itself (cleaner to do it on Students table as opposed to Attendance Log table since the former has exactly one copy of each student whereas the latter has a copy of each student for each time they are recorded as present or absent) which gives me each students' attendance in that period. I then filter by students whose attendance is below the threshold and only display these names.

Thank you so so much! You've been an invaluable help 🙂

[Suvrutt_Gurjar]

Thank you for the update. Good to know all works per your requirement.