
Problem
I need Monthly reporting to show the amount of Opportunities are open this month. This could also be done for Year-over-Year reports.
Solution
You can use Rollup Helper and Lookup Helper together. For this example, we will do monthly reporting.
- From Setup > Object Manager
- Create a Reporting Month custom Object
- Create Lookup relationship Field from Opportunity to Reporting Month, called Reporting Month
- Create new Formula Field on Opportunity to pull Month and Year from an Opportunity, called Reporting Month Formula
- Set return type to Text
- Use "Advanced Formula"
- TEXT (MONTH (CloseDate)) + "-" + TEXT (YEAR (CloseDate))
- Create new Currency Field on the Reporting Month Object, called Open Opps Closing this Month
- Set number decimal places to 2
- Go into Lookup Helper to automatically relate Opportunities to Reporting Months
- Create new Lookup Helper setting
- Select Child Object as Opportunity
- Maintain Existing Lookup Relationship, using the Reporting Month field
- Field on Opportunity you want to match is the Reporting Month Formula field
- Enable Create New Category Records
- Save and run setting
- Use Rollup Helper to aggregate Opportunity information on the Reporting Month
- Create new Rollup Helper setting
- Target Object is Reporting Month
- Target Field is Open Opps Closing this Month
- Child Object is Opportunities
- Select Type of rollup as Sum
- Field on Opportunity should be Amount
- Create new filter to choose only Opportunities closing that month
- Filter criteria should be Stage, not equal to, Closed Won
- Another filter criteria is Stage, not equal to, Closed Lost
- Save filter
- Save and run rollup
You can also watch this video walkthrough for more details.
How to Categorize Customers by Order Date in Salesforce | Rollup Helper Use Case Library | Number of Open Projects on an Opportunity |