0

I am trying to create a formula which will calculate the revenue an opportunity is going to bring in this fiscal year.

The main issue I am running into is creating a formula which does not exceed the 5000 character limit. In the explanation bellow I will explain the situation for PRODUCTS_VALUE, but I also have a field called SERVICES_VALUE which is identical, which is the reason I am going over the 5000 character limit.

Essentially, to calculate the revenue I have a field called PRODUCTS_VALUE.

I then have something called revenue trigger (REVENUE_TRIGGER) which is either "on completion" or "monthly". This will either trigger the revenue to be received starting monthly after the job begins until the job ends, or simply when the job ends. For monthly revenue trigger, the monthly revenue is entered.

I then have lead time to start (LEAD_TIME_TO_START_PRODUCT) which determines the amount of time between the opportunity being closed and the job beginning, and then lead time to complete (TIME_TO_COMPLETE_PRODUCT) which represents the amount of time it takes from the job beginning to be complete.

Basically broken down into pseudo-code it would be:

if revenue_trigger = "on completion" then
   revenue_date = Closed/Won Date + Lead Time To Start + Time To Complete
   if revenue_date is this fiscal year then
        revenue_fiscal_year = products_value
else if revenue_trigger = "monthly" then 
    revenue_start_date = Closed/Won Date + Lead Time To Start 
    number_of_months = products_value / monthly_revenue
    current_month = revenue_start_date
    while current_month < revenue_start_date + number_of_months
        if current_month is in this fiscal year
            revenue_fiscal_year += monthly revenue
        else
            exit while loop

Given this data, is there any way to construct a formula which will determine the amount of revenue an opportunity will generate this fiscal year and also be inside the 5000 character limit? I realize this is a pretty big question to be asking.

Here is the Salesforce formula I have come up with roughly that is far too large and not exactly accurate. Not some of the names are slightly different.

IF ( TEXT(Revenue_Trigger__c) = "On Completion", 
        ( IF ( YEAR(Stage_6_Complete__c + (Lead_Time_Start__c   * 7)) = YEAR(TODAY()) , 
            Product_Value__c, 0)
        ),

        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 360) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 360) ),
            Monthly_Revenue__c * 12,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 330) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 330) ),
            Monthly_Revenue__c * 11,    
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 270) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 270) ),
            Monthly_Revenue__c * 9,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 240) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 240) ),
            Monthly_Revenue__c * 8,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 210) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 210) ),
            Monthly_Revenue__c * 7,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 180) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 180) ),
            Monthly_Revenue__c * 6,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 150) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 150) ),
            Monthly_Revenue__c * 5,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 120) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 120) ),
            Monthly_Revenue__c * 4,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 90) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 90) ),
            Monthly_Revenue__c * 3,
        IF ( YEAR( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + 60) = YEAR(TODAY()) 
            && ( ( Stage_6_Complete__c + (Lead_Time_Start__c * 7) + ( Lead_Time__c * 7) ) >= ( Stage_6_Complete__c + (Lead_Time_Start__c    * 7) + 60) ),
            Monthly_Revenue__c * 2, 0       
)))))))))))
  • 1
    Workflow field updates have higher limits. That might be an approach. Also any independent values can be calculated using a field update, like revenue_date. That would reduce the compile size of the formula. Field updates can fire in any order so the items must be independent, if it's not calculating the whole thing. – gorav Jan 13 '17 at 20:33
  • 1
    Maybe try process builder to independently calculate some of the fields into currency fields and then do formulas on that. Else this has to be solved by some trigger magic. – Dan Wooding Jan 13 '17 at 21:05
  • 1
    nightly scheduled apex batch job can be a clean way to update the revenue forecast with the added advantage that you can write regression testmethods to prove your implementation – cropredy Jan 14 '17 at 0:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.