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 ?
[SalesForce] Date filter in child record while creating roll up summary field
Related Solutions
This answer assumes that the parent child relationship between Test Plan and Sub Test Plan only goes one level deep. I created a hyperlink formula field on the Test Plan to link to the Parent record which will either be itself or Master Test Plan when available.
HYPERLINK("/"&BLANKVALUE(Master_Test_Plan__r.Id, Id), BLANKVALUE(Master_Test_Plan__r.Name, Name))
I then used this formula field to summarize the data on a Test Plan report to sum the pass and fails of the Sub Test Plans as seen in this image:
Same report with details:
Native roll-up summary fields can only be created based on, and only update based upon changes to, non-formula fields of specific types that are located on the object being rolled up. As a consequence, you can't build a rollup from Opportunity to Account based on field data on a third object, Show.
Additionally, the comparison criteria for a Roll-up Summary Field cannot be dynamic - e.g., you can't compare a field value against THIS_YEAR or some other field value. Criteria must be comparisons against a static value, like "2019".
There's (at least) three possible solutions, and this can be done declaratively, although the declarative solution without DLRS is a little bit complex.
One is to use Process Builder or Process Builder and Flow to update fields directly on the Opportunity when the associated Show changes, so you can use a native roll-up summary field to total the relevant Opportunities.
You'd probably need a Checkbox field like
This Year's Showon the Opportunity to use in your filter, given the criteria limitations mentioned above, and your Process could populate this by taking into account all the criteria you need from the Show.It sounds like there is a scheduled component to this too - in January, you want to make sure that all of the roll-up fields reflect the new year. This could be accomplished with Process Builder, again, using Time-Dependent Actions. You'd need to have a calculated Date formula field for "First Day of Following Year" on the Show, have your Process fire on creation, and execute a scheduled action 0 days after "First Day of Following Year". The action would then perform an update on all the related Opportunities to change the field that's being used to roll them up.
Another is to write a fair amount of code that does largely the same workflow as described above, including both triggers and Scheduled Apex to address the time-delayed workflow.
Probably the easiest solution is to use Declarative Lookup Rollup Summaries. You can write a rollup in DLRS, using Scheduled mode, that will refresh on a nightly basis, and you can roll up there on a criterion that includes a formula field like "Show__r.Year__c = THIS_YEAR".
Best Answer
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.