1

I'd like to create on my Account object a rollup which sums the "total CustomObjectC amount" which have been custom_field_date in last week.

My first approach was to create a formula fields that says:

Last Week Amount = IF(Today() -7 < Custom_Field_Date__c, Amount__c,0)

and then try to roll it up, but this field doesn't appear in the list of field that can be rolled. What is the right way to solve this?

1

2 Answers 2

2

Even if it's master detail, you can still switch over to Declarative Lookup Rollup Summaries (DLRS). I was recently able to calculate a YTD rollup using the following value in the Relationship Criteria field:

My_Date_Field__c = THIS_YEAR

Don't forget you must also specify My_Date_Field__c in Relationship Criteria Fields for this filter to work. Also in your specific instance you would use a different Date Literal in place of THIS_YEAR, namely LAST_N_DAYS:7.

Note that it's even recommended by the product team in this Idea:

Roll-Up Summary Fields: Formulas Rather than Static Criteria

Latest Comment from Salesforce

Hi Everyone- quick update from product management, please see below

Using a formula in Roll-up Summary Fields is something we'd like to do but we have a few priority items ahead of it so it hasn't been prioritized on our current roadmap.

In the meantime, some customers have suggested the Declarative Rollup Summary Tool as an alternative (it is free). Per one comment, "When setting up a new rollup with this tool you can specify relationship criteria for querying the child object with a typical SOQL where clause." We have not tested this ourselves, but did want to provide the link so you can research and potentially use it should it work for your particular scenario.

https://developer.salesforce.com/page/Declarative_Rollup_Summary_Tool_for_Force.com_Lookup_Relationships

We'll keep this Idea open and actively tracking on it so that we can post again when and if there are any new updates.

1

Looks Relative Dates like not possible with RUS (One of those gotchas that is easily forgotten).

Formula fields do not appear in the fields to pick so you need to create a boolean field and have code update it daily...Then use that field in the RUS filter criteria

If the custom_field_date is a date field,

In the RUS definition filter criteria select that field then set the criteria to be equal to LAST 7 DAYS

When you create a list view that includes a filter for a range of dates, such as Create Date or Last Update Date, use one or more of the relative date values provided to specify the range.

https://help.salesforce.com/articleView?id=custom_dates.htm&type=0

6
  • what if it's datetime? Apr 20, 2017 at 21:27
  • Should work as well
    – Eric
    Apr 20, 2017 at 21:28
  • in the filter criteria I get an error invalid date. Valid date format xyz... Apr 20, 2017 at 21:32
  • @Edmondo1984 - Ugh, looks like RUS is an exception.....
    – Eric
    Apr 20, 2017 at 21:35
  • that's why I asked and why I tried to use formulas Apr 20, 2017 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.