+ Start a Discussion

SOQL statement for aggregating amounts of opportunities and grouping by fiscal quarter.

I have the following SOQL statement:


    fiscal_quarter(olis.ScheduleDate) fquarter, sum(olis.Revenue) revenue
    OpportunityLineItemSchedule olis
    olis.ScheduleDate = THIS_FISCAL_YEAR
group by 

 which returns the aggregated revenue amount, grouped into fiscal quarters, of all opportunities with a scheduleDate that falls in the current fiscal year.


Unfortunately, I've realized that this query does not include amounts from opportunities without a schedule information (ie., opps without oppLineItemSchedules children). 


To provide a contrast with SF reports, if you have a report of report type, Opportunities with Products and Schedules (out of the box report type), grouped by fiscal quarter, the amounts from all opportunities are grouped into fiscal quarters based on two observed conditions:

  • Amounts from opps with schedule info are allocated to fiscal quarters according to  oppLineItemSchedule.ScheduleDate.
  • Conversely, amounts from Opps without schedule info are allocated to fiscal quarters based on Opportunity.CloseDate

My SOQL statement above currently cannot achieve the second condition. How can I modify it to do so?