You need to sign in to do that
Don't have an account?

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?