Apex Code Development You need to sign in to do that
Don't have an account?
Johan Emerén 2 Field calculations
Hi,
I'm having trouble calculating the value of an opportunity based on the end of the year.
So if OP X has an estimated live date 02/02/2018 I'd like to see the amount that would generate until the end of the year.
Months left is easy "13 - MONTH(TODAY())"
And the value should be something like this but it doesnt seem to work "Opportunity_Value__c *(MONTH(Estimated_Live_Date__c) + (12*(YEAR(Estimated_Live_Date__c)-YEAR(TODAY()))) - MONTH(TODAY()))"
Can someone help me please?
Amount * (( DATE(YEAR(CloseDate),12,31) - CloseDate ) / 30) formula works fine for me.
Also it will do what you want i.e Value * (amount of months between estimated live date until the end of the year) as we have divided the no of days by 30 so indirectly we are converting it to months from days.
So replacing the CloseDate with another date field and the Amount with another number/ currency field should not be a problem.
Please find the below snap for the same:
Please let me know if it helps.
Thanks,
Jainam Contractor
All Answers
Please find the below formula that i used to Calculate the Opportuntiy Value based on the CloseDate and Amount.
Custom Field: Opportunity Value
API Name: Opportunity_Value__c
Opportunity_Value__c =
Amount * (
(
MONTH(CloseDate) + 12*(YEAR(CloseDate) - YEAR(TODAY()))
)
- MONTH(TODAY()) + 1
)
Eg: If Amount = $10,000, CloseDate = 06/30/2018, Today's Date = 1/24/2018 then the Value for Opportunity_Value__c = $60,000
Please find the below snap for the Same:
Please let me know if it solves your problem and if you need any more assistance. Please mark this answer as the Solution if it solves your problem.
Thanks,
Jainam Contractor,
Salesforce Consultant,
Varasi LLC
www.varasi.com
Hi Jainam,
Thank you for the thourough explanation and assistance.
I may have explained myself incorrectly, the value should be the amount this opportunity would generate from it's estimated live date until the end of the year. This seems to calculate the value from now until the ELD.
But maybe I did something wrong.
Also, would it be possible to take the days into account? Since the estimated live date here is the 31/10 that month should preferably not count as a full month.
Yes we can consider days as well. Please look at the below formula:
Amount * ( DATE(YEAR(CloseDate),12,31) - CloseDate ), this considers CloseDate and Amount.
For your case, you can use something like this:
Opportunity_Value__c * (( DATE(YEAR(Estimated_Live_Date__c),12,31) - Estimated_Live_Date__c) / 30).
This will intially calculates the number of days left in that Year and then convert into Approximate Month by dividing by 30.
Please let me know if it solves your problem and mark it as the best answer if it does. Please let me know if any concerns.
Thanks,
Jainam Contractor.
Amount * (( DATE(YEAR(Estimated_Live_Date__c),12,31) - Estimated_Live_Date__c) / 30)
This gives me a syntax error, my inexperienced guess would be that it attempts to take the amount times a date value.
Aside from that it looks like it should do what I want
Value * (amount of months between estimated live date until the end of the year)
Amount * (( DATE(YEAR(CloseDate),12,31) - CloseDate ) / 30) formula works fine for me.
Also it will do what you want i.e Value * (amount of months between estimated live date until the end of the year) as we have divided the no of days by 30 so indirectly we are converting it to months from days.
So replacing the CloseDate with another date field and the Amount with another number/ currency field should not be a problem.
Please find the below snap for the same:
Please let me know if it helps.
Thanks,
Jainam Contractor
Thank you so much for your help, this works perfectly.
Now if I wanted to go a step further and have this take into account ramp up, would that be possible?
The first six months would generate the following of the "Amount" field.
10%
20%
40%
60%
80%
100%