Conditional Rollup based on Date


#1

Hi! We run a diaper pantry and give away about 12,000 diapers every month to local families.

We have two types of clients – monthly clients who can come every month, and “emergency” clients who can come 3x per calendar year.

This is how we have been tracking the emergency counts:

We have a table called “Diaper Pickup”. When a client comes in who’s classified (in the “Clients” table as an Emergency client, a Rollup field pulls from the Client table and does a simple Sum of how many times that Client has picked up.

We started this in 2018 and now that we’re in a new calendar year, would like to reset the values. I think we need a conditional rollup but I suck at formulas. Can someone help me with a formula that’s like “If after 1/1/2019, sum this amount”?

Please let me know if I need to clarify anything. My brain feels like it’s breaking and I can barely name every day objects at this point.

Many thanks in advance for any suggestions!


#2

You’re on the right track! I would create a new field on the Clients table with the following formula (replace “{Date}” with the name of your Date field).

IF({Date} = BLANK(), 0, IF(YEAR({Date}) = 2019, {Diaper Count}, 0))

Then perform your rollup on that field to sum up only 2019 counts.