I have written the following class below to calculate the Revenue per Month given a Start and End Date and A production Revenue. However, I don't think this is the most efficient way. In fact I think this will take quiet a bit of processing power. Secondly, the last step would be to display the opportunity and its revenue over the months. Should I use a nested list with each containing String Name and a Map and iterate through all the opportunities that way in order to display the results into a visual force page report?

Essentially is there a way I can improve this?

public class revenueAggregationByMonth {
     /*   //List the Months in a year as a list
        private final List<String> MONTHS_OF_YEAR = new String[]

        // Create Key value pair for Month and Revenue Values; Months must be in order; 
         public static map <Integer,Decimal> Month_Revenue = new map<Integer,Decimal>{
        1 => 0.00,
        2 => 0.00,
        3 => 0.00,
        4 => 0.00,
        5 => 0.00,
        6 =>0.00,
        7 => 0.00,
        8 => 0.00,
        9 => 0.00,
        10 =>0.00,
        11 =>0.00,
        12 =>0.00


    public static Integer getBusinessDays(Datetime startDate, Datetime endDate) {
        //Work out days in range
        Integer days = startDate.date().daysBetween(endDate.date());

        //Remove most weekends by removing 2 in 7 days (rounded down)
        days -= ((integer)Math.floor((days / 7)) * 2);

        if (startDate.format('E') != 'Sat') {
            days -= 1;
        } else if (startDate.format('E') != 'Sun') {
            days -= 2;

        return days;

    public static void GettOpps(Date Report_StartDate, Date Report_EndDate)

    List<Opportunity> allopps = [SELECT Account.Name, Name, Start_Date__c, End_Date__c, Estimated_Production_Revenue_Won__c
FROM Opportunity WHERE (Start_Date__c >= :Report_StartDate AND Start_Date__c <= :Report_EndDate)
OR (End_Date__c >= :Report_StartDate AND End_Date__c <= :Report_EndDate)];

        for(Opportunity o: allopps)
            if (o.Estimated_Production_Revenue_Won__c > 0 && getBusinessDays(o.Start_Date__c, o.End_Date__c)> 0)
                decimal Revenue_per_day = o.Estimated_Production_Revenue_Won__c/getBusinessDays(o.Start_Date__c, o.End_Date__c);
                Decimal days_between = 0.0;
                Decimal Total_rev_per_month = 0.0;
                Date agg_start_Date = o.Start_Date__c;
                While (agg_start_Date <= Report_EndDate )
                    Date agg_end_date = EndofTheMonth(agg_start_date);
                    days_between = getBusinessDays(agg_start_date, agg_end_date);
                    Total_rev_per_month = days_between * Revenue_per_day;
                    for (Integer m: Month_Revenue.keySet())
                        Month_Revenue.put(agg_end_date.month(), Month_Revenue.get(agg_end_Date.month())+total_rev_per_month);
                    agg_start_date = agg_end_date.addDays(1); 



    public static Date EndOfTheMonth(Date selectedDate)
        Date firstDate = selectedDate.toStartOfMonth();
        Date lastDate = firstDate.addDays(date.daysInMonth(selectedDate.year() , selectedDate.month())  - 1);
        return lastDate;

  • Why not make Formulas that display the Start Month and End Month and then make a Report where there are conditions Start Date >= 20xx-xx-xx and End Date <= 20yy-yy-yy and group by Start Month / End Month? – Scott Pelak Jun 11 '15 at 21:17
  • @AmatorVitae Can you please elaborate on that? The Start and End Date already Exist. But it won't divide up the revenue evenly by days. – molly_567 Jun 11 '15 at 22:01

The biggest issue you will run into is trying to generate this data for a large dataset and/or period of time. Eventually you will run into either a record limit, or a heap size limit (because you'll have so much data to store).

An alternative would be to store the monthly revenue values on a trigger on opportunity line items and store this data against those records. In saying that, the most logical place to then store this value would be in opportunity product schedules. OOB schedules work for evenly splitting the value, however since you are calculating intra-month values, you will want to set up custom schedules.

A quick word on setting up custom schedules: They are a pain to update. When it comes to updating the schedules, it's actually easier to delete all the records, and recreate them - rather than updating them. You'll find out why as soon as you start trying to develop them.

Why would this help? Because you store the data as soon as it's created/updated on a per record basis. Triggers will bulk the records into 200 record blocks to process for you - so you should never really hit any governor limits. Data is live in the reports interface, and you can use OOB reports to look at the info.

Refactoring your code? What you have is how I'd build it. I may do some small things slightly similar, but you're always going to hit problems when you start deal with large amounts of data.

  • We do not use opportunity line items or products for our opportunities. – molly_567 Jun 12 '15 at 13:45
  • Ah right. Well in that case, I would suggest a new object to hold your monthly value for the opportunities, and still calculate it at the point of insert/update. It will reduce the overhead of the complex calculation in real time, and it would allow you to use OOB reports, rather than custom pages. – Ben Naylor Jun 15 '15 at 4:18

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.