function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jaslozjasloz 

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


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

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{

    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);
                }
                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);
                }

            //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;
                }
            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 ;
        } 
    }
}