function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
jjulianjjulian 

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

I have the following SOQL statement:

 

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

 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?