0

I have a list of products with associated schedules where each schedule has different Revenue and ScheduleDate values. I'm trying to get the get the aggregate of Revenue by DATE across the OpportunityLineItems. However, i'm not sure how to achieve this as i couldn't find any date functions specific to date while DAY_ONLY() will only work for datetime fields.

Example - I have an opportunity which has - lets say 6 products (prod count varies by oppty) with associated schedules. I'm trying to insert a new product(Name - AGGREGATE PRODUCT), whose schedules must show the aggregate of all 6 product's schedules by "ScheduleDate".

I dont want to FOR loop over all the schedules of those 6 products & create a map with Map<Scheduledate, Sumof(revenue)> which would consume more cputime causing exception. Thought if there is any aggregateResult that can be used to get the sumof Revenue by Scheduledate.

Please share your thoughts & recommendations.

Thanks!

6
  • Have you tried using GROUP BY ROLLUP? Jun 10, 2020 at 23:41
  • Thanks for the response @HengkyIlawan . However i'm confused on how to get the actual working soql. Actually I have a VFP with 2 date input fields(FROM and TO) which is kind of a date filter, when set - must pull the oppty & aggregate of "scheduleAmount" for that particular timespan(filter dates). Which is something like Map<OppId, SUMof(ScheduleAmountFORSetTimeFrame)> Really appreciate your help!
    – SMorse
    Jun 11, 2020 at 1:38
  • in that case I think you can use the aggregate query with SUM function, right? Jun 11, 2020 at 2:10
  • for(AggregateResult aggr : [SELECT sum(Revenue), scheduledate, opportunitylineitem.OpportunityId FROM OpportunityLineItemSchedule WHERE opportunitylineitem.OpportunityId = '006W000000GVfpuIAD' GROUP BY opportunitylineitem.OpportunityId, scheduledate]){ system.debug('---oppId----'+(Id)aggr.get('opportunitylineitem.OpportunityId')); system.debug('---'+(Double)aggr.get('expr0')); } Following is the error - System.SObjectException: Invalid field opportunitylineitem.OpportunityId for AggregateResult
    – SMorse
    Jun 11, 2020 at 2:24
  • I copy paste your code, and it runs ok. That aside, since your query is filtered by opportunity Id, it doesn't have to be the grouping field. Jun 11, 2020 at 6:37

0

You must log in to answer this question.