Im trying to do something thats plagued me for years in salesforce reports. It feels like it should be relatively simple but for some reason i struggle to get it to actually work. My actual setup is a little bit different but I'm going to try and use a simpler example of the issue.
Lets say you have an account with a series of opportunities, and what you want to know is the amount change (in a percent) between the initial opportunity and the most recent one, ordered by created date.
My hope is to be able to build a report of Accounts and Opportunities which would allow me to set any date range (Q2 for example) and have a formula field that would calculate the percent change in the amounts between the first opportunity in that range and the last.
I am able to get something similar to this using Opportunity.Amount:Min and Opportunity.Amount:Max but the issue is often times the first opportunity in a date range is not the smallest one, when what i really want is the change between the first and the last.
Feels like I would need some kind of VLookup function or some way to do something like Opportunity.CreatedDate:Min.Amount - Opportunity.CreatedDate:Max.Amount
So a simple example for company Acme Corp has the following opportunities
- Opp 1 - $500 - 1/1/2014
- Opp 2 - $1250 - 3/1/2014
- Opp 3 - $100 - 4/4/2014
- Opp 4 - $1000 - 5/7/2014
What im looking for is a way to automatically calculate the difference between the first opp and the last one, so $1000 - $500 = $500 or a 100% increase
The reason i want this in a report is so if i change the date range in the report to be 2/1/2015 - 5/10/2014 it would update the formulas to be $1000-$1250 = -$250
Any ideas?
Opportunity.Amount
of the record with the newest CreatedDate - record with the oldest CreatedDate. The Max and Min Amounts have nothing to do with what you're looking for. It would appear that you want the Amounts for the Opps with Max and Min Dates that fall within your range.