ShowAll Questionssorted byDate Posted
jasloz

# Opportunity In Year Value calculation

Problem
We are looking to create a custom formula in the Opportunity Object.

For each opportunity we record the 'Opportunity Start Date' and 'Opportunity End Date'; these dates indicate the term of the opportunity which may be as short as a month or as long as 5 years.  We are looking for a formula to calculate (on a prorata basis) the Revenue expected in the Current Fiscal Year.

Fields that are relevent
'Opportunity Start Date' API = Opportunity_Start_Date__c - This is the expected works start date, not necessarily the same as the Close Date.
'Opportunity End Date' API = Opportunity_End_Date__c - This is the expected works end date.
'Amount' API = Amount - This is the order amount.
'In Year Value' API = In_Year_Value__c - This is the value of revenue expected from the opportunity in the current Fiscal Year.

Ideal Solution
The formula should be as simple as

In_Year_Value__c = (Amount / (Opportunity_End_Date__c – Opportunity_Start_Date__c)) * (min(Opportunity_End_Date__c, Current_Fiscal_Year_End) – max(Opportunity_Start_Date__c , Current_Fiscal_Year_Start))

However Salesforce has a number of issues with this formula.

1) Max & Min cannot be used on Dates.
2) There is no ability to use Current_Fiscal_Year_End in the formula

I have not created any custom development at this time but am happy to give anything a go to get this to work as it really is a business critical calculation.

Jason

Message Edited by jasloz on 06-29-2008 02:43 AM
Jakester
www.ezsaas.com did almost this exact customization for us, and we were extremely pleased with their work. Highly recommended!!
jasloz

I have managed to get Apex to work out the InYearValue (and NextYearValue) and have created a Trigger to run the code on Insert/Update (this was done on my developer licence).

However when I try to save the code into our live environment (using Eclipse) I get the following message.

‘Average test coverage across all Apex Classes and Triggers is 0%, at least 75% test coverage is required’

Can anyone help me with code for the test coverage?

Code:
```public class CHt_In_Year_Value{

{
date startdate;
date enddate;
date fstartdate;
date fenddate;
date fstartdate1;
date fenddate1;
double amount;
double inyearvalue;
double nextyearvalue;
long totaldays;
long fiscaldays;

for (Opportunity o:ops){

//Workout fiscal year start and end based on current system date.
//This works where fiscal start is 1st April.
//Need a seperate application to update all opportunities once fiscal year changes on 1st April.

if (System.Now().month() >3)
{
fstartdate = date.newinstance(System.Now().year(),04,01);
fstartdate1 = date.newinstance(System.Now().year()+1,04,01);
fenddate = date.newinstance(System.Now().year()+1,03,31);
fenddate1 = date.newinstance(System.Now().year()+2,03,31);
}
Else
{
fstartdate = date.newinstance(System.Now().year()-1,04,01);
fstartdate1 = date.newinstance(System.Now().year(),04,01);
fenddate = date.newinstance(System.Now().year(),03,31);
fenddate1 = date.newinstance(System.Now().year()+1,03,31);
}

startdate      = o.opportunity_start_date__c;
enddate        = o.opportunity_end_date__c;
amount         = o.amount;

// If enddate, startdate or amount is NULL then return an InYearValue of 0.
if (enddate == NULL || (startdate == NULL || (AMOUNT == NULL)))
{
inyearvalue = 0;
nextyearvalue = 0;
}
ELSE
{
// Simple calculation to work out opportunity days
totaldays = startdate.daysBetween(enddate)+1;

//Logic to work out days in Fiscal year
if  (fenddate < o.opportunity_end_date__c)    enddate = fenddate;
if  (fstartdate > o.opportunity_start_date__c)     startdate = fstartdate;
fiscaldays = startdate.daysBetween(enddate)+1;
// If InYearValue < 0 (due to dates being earlier or later than fiscal year etc make value = 0
if (inyearvalue < 0) inyearvalue = 0 ;
inyearvalue = (amount / totaldays) * fiscaldays;
//Logic to work out days in Next Fiscal year
if  (fenddate1 < o.opportunity_end_date__c) enddate = fenddate1; else enddate = o.opportunity_end_date__c;
if  (fstartdate1 > o.opportunity_start_date__c) startdate = fstartdate1; else startdate = o.opportunity_start_date__c;
fiscaldays = startdate.daysBetween(enddate)+1;
// If nextYearValue < 0 (due to dates being earlier or later than fiscal year etc make value = 0
if (nextyearvalue < 0) nextyearvalue = 0 ;
//Formula to prorate Revenue on straight line across days in fiscal year
nextyearvalue = (amount / totaldays) * fiscaldays;
}
//Update Opportunity Record
o.In_Year_Value__c = inyearvalue ;
o.Next_Year_Value__c = nextyearvalue ;
}
}
}```