Date filter in child record while creating roll up summary field

Question

Is there any way that we can put the date filter on child record (like created in last one year) while creating roll-up summary field? I want to count only those child records which are created within last one year. I tried it by creating formula field on child object but it does not appear while filtering the records (As per the doc: Automatically derived fields such as current date or current user are not allowed in roll-up summary fields). Any workaround if someone face this issue before ?

Answer 1

As far as I know it can't be done with normal rollup. You would expect any TODAY()-related stuff to magically move when the boundary date passes (or in your case - January 1 comes). But the rollups aren't calculated when you view the record like formulas are, they're calculated when child record is updated.

So you need some way to "touch" all records at some point (by touch I mean an update that doesn't change anything, edit & save). And you'll need either time-based workflow for that or a scheduled batch job. Time-based sounds easier but even then you have to submit to the queue all your existing records so they aren't left forgotten when the time comes.

Answer 2

I have a pretty easy solution for this one, but its not very scalable.

Create a formula field on the child records that checks the date of this entry and if its after the specified date, then it gets the full value otherwise it gets 0. Then just use a regular roll up summary to calculate that total. You can hide that field from users so they don't realize there is an extra calculation.