Conditional Rollup based on Date

1360 1
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Reply 1
10 - Mercury
10 - Mercury

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.