I almost have my data where I need it, but I’m missing one important thing. I have a table of households and then I have a table of event attendance. I can generate a report of all events attended by a household. I can also summarize all events into attendance numbers and which households attended.
What I need to be able to do is see what households attended events within a date range, and how many events. I can scroll through each individual household, but I want to display this all on one table.
So far, I can generate a report for a date range, but it will return each household as many times as they attended. I would like them to be only listed once.
I can also summarize household attendance and show each household and how many total events were attended, but I can not sort this table by date range. When I try to make a date column I can get a list of dates in one cell, but then I can not do a date search on this column.
So I can do the two halves of my problem separately, but I can not figure out how to do them both at once.
Any ideas?
This may not be the most elegant solution, but I would make a separate table called “Date Range” with a column for the start of the range and a column for the end of the range. Just have the one row. Then you can call lookupOne to make a True/False column in your Events table, where the column will tell you if that event is in your date range or not. From there, you can filter your Household names based on a lookup that returns any events where 1. They were in attendance and 2. The event was in the date range.
I’m sure I’m explaining this poorly, so here’s a link to a simple version I made so you can see what I’m talking about.
Making another reply to link directly to the official Grist answer to this question!