I need to deliver count and sum based on date ranges in my table. I currently have create date, booked date, complete date and convert date for a meeting tracker.
I am trying to find the best methodology for reporting this data on a weekly, monthly, quarterly and annual bases (as well as M/M, Q/Q and Y/Y for comparison).
For example:
All meetings booked this week/month/quarter
All meetings completed this week/month/quarter
All meetings converted this week/month/quarter
And then keep this data to use in month over month, quarter over quarter and year over year comparisons.
Is their a best way in Glide to implement a “where clause” (e.g. where complete data is after May 22 and before May 29) so I can change the dates to a week/month/quarter/etc and get consistent reports?
I think the cleanest structure is to have multiple tables where you have a list of weeks (let’s say week 1 - 2022, week 2 - 2022 etc), a list of months, a list of quarters, a list of years etc.
Then for each record for your meeting, use computed columns to know which week, month, quarter, year that the meeting was in.
In the “time” tables, create relations and rollups to get the number you want.
Close to what I did. I created a weekly table in sheets with an array column Day 1 Day 2…Week x, Month, Quarter and Year
Then I have a relation based on the meeting date to the DAY array column and I “get” the Week, Month, Quarter and Year info. Created 3 years of business data (and I can rework the underlying days so it will work for companies with non-standard quarters (e.g. FEB/MAR/APR instead of JAN/FEB/MAR))
I created an artificial index using ‘template’ to create a ‘key’ in the meeting so I can roll-up the Week/Month/Quarter meeting info.