+ Start a Discussion

Opportunity In Year Value calculation

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.
Many thank in advance.

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

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?

public class CHt_In_Year_Value{

    public static void addInYearValue(Opportunity[] ops) 
        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);
                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);

            //Get required fields from opportunity Object that is being updated/created.
            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;
                // 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 ;