Jan 17, 2022 02:04 PM
Hi Everyone,
I had previously accomplished this in Google Sheets, but since moving to Airtable, I’ve had a bit of trouble replicating it.
We’re a dog training company, and want to count the number of packages that each trainer sells in a given month.
My table has a column with a date field that contains when the package is sold, a column with the trainer’s name (a linked record to a trainer’s table), and a column if the package has been paid for.
Previously in Google Sheets, I used a table that contained the date range for each month (ex. 1/1/22 to 1/31/22). The formula would see if the date the package was sold was between these dates, checked the trainer’s name matches, and if the payment contained the word “Paid”.
Would love some help trying to get this working with Airtable :slightly_smiling_face:
Thank you
Solved! Go to Solution.
Jan 17, 2022 08:52 PM
Welcome to the community, @Owen_Marshall! :grinning_face_with_big_eyes: Create a formula field named {Sale Month}
using this formula (replacing {Date Sold}
with the name of your date field that marks the date the package was sold):
DATETIME_FORMAT({Date Sold}, "MM-MMMM")
That will give you output like “01-January”, “02-February”, etc. for each record. Create a new view that groups records by that field, and then by trainer (you can hide the {Sale Month}
field at this point, as the group header will show it). Filter the view to only show records that are marked as paid. Within each month group you’ll have one group per trainer that indicates how many packages they sold in that month.
Jan 17, 2022 08:52 PM
Welcome to the community, @Owen_Marshall! :grinning_face_with_big_eyes: Create a formula field named {Sale Month}
using this formula (replacing {Date Sold}
with the name of your date field that marks the date the package was sold):
DATETIME_FORMAT({Date Sold}, "MM-MMMM")
That will give you output like “01-January”, “02-February”, etc. for each record. Create a new view that groups records by that field, and then by trainer (you can hide the {Sale Month}
field at this point, as the group header will show it). Filter the view to only show records that are marked as paid. Within each month group you’ll have one group per trainer that indicates how many packages they sold in that month.