Creating a view for client attendance count per month

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.

Welcome to the community, @David_Dunford! :smiley: 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:

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.

2 Likes

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.