Creating a view for client attendance count per month

Topic Labels: Views
1035 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi everyone,
I am new to airtable and trying to create a view that will give me a ‘count’ of how many times a client attended a session in the month.
I have linked the ‘client’ table and select all the clients that attended for each session. As can be seen, I have also added an ‘attendance months’ field so I can group by month.

I am having trouble trying to structure one of the tables to provide the count for each client for the given month.
Any help is appreciated, thanks.
Screen Shot 2021-12-11 at 11.12.17 am

1 Reply 1

Welcome to the community, @David_Dunford! :grinning_face_with_big_eyes: This can be done using rollup fields in your [Clients] table. Using November 2021 as an example, create a rollup field named something like {2021 Nov}, with this configuration:

Screen Shot 2021-12-10 at 5.42.13 PM

Here’s the aggregation formula so you can copy it for testing:

IF(values, LEN(SUBSTITUTE(ARRAYJOIN(values, ""), "2021 (11) Nov", "x")))

That formula takes the collection of all month labels from the attendance table for a given client (the contents of the “values” variable), mashes them all together with the ARRAYJOIN() function, replaces each instance of the label with a single character (it doesn’t have to be an “x”, as long as it’s just a single-character string), then returns the length of the result.

Screen Shot 2021-12-10 at 5.46.46 PM

Screen Shot 2021-12-10 at 5.46.29 PM

Duplicate that rollup field as many times as you want, changing the label string replaced by the SUBSTITUTE() function with the string for another month.