I have this below SAQL. What i want in end result is

sum(q_A.'Revenue' * q_A.'Opportunity.Probability')/100) for each month

basically Revenue is OpportunityLineSchedule field and Probability is Opportunity field. I want for sum((Revenue * Probablity)/100) for each month

q = load "TestDataset";
q = filter q by 'Opportunity.Type' in ["A", "B", "C", "D"];
q = filter q by 'OpportunityLineItemId.Test__c' != "Transport";
q = filter q by date('ScheduleDate_Year', 'ScheduleDate_Month', 'ScheduleDate_Day') in ["current year".."current year"];
q_A = filter q by 'Opportunity.IsWon' == "true" && date('Opportunity.CloseDate_Year', 'Opportunity.CloseDate_Month', 'Opportunity.CloseDate_Day') in ["1 quarter ago".."current quarter"]; 
q_C = filter q by 'Opportunity.IsClosed' == "false";
r = load "Test2Dataset";
r = filter r by date('Quarter_Start_Date__c_Year', 'Quarter_Start_Date__c_Month', 'Quarter_Start_Date__c_Day') in ["current year".."current year"];
result = group q_A by ('ScheduleDate_Year', 'ScheduleDate_Month') full, q_C by ('ScheduleDate_Year', 'ScheduleDate_Month') full, r by ('Quarter_Start_Date__c_Year', 'Quarter_Start_Date__c_Month');
result = foreach result generate coalesce(q_A.'ScheduleDate_Year' + "~~~" + q_A.'ScheduleDate_Month', q_C.'ScheduleDate_Year' + "~~~" + q_C.'ScheduleDate_Month') as 'ScheduleDate_Year~~~ScheduleDate_Month', sum(q_A.'Revenue' ) as 'A', max(q_A.'Opportunity.Probability') as 'C';
result = order result by 'ScheduleDate_Year~~~ScheduleDate_Month';
result = limit result 2000;
  • one workaround you might consider is creating a formula field on the schedule object that does the multiplication for you. then summarize that in your saql. haven't tried it, there could be limitations around aggregating a formula field – Ralph Callaway Apr 2 at 18:44

What you will need to do is aggregate each measure separately. The following should work.

sum(q_A.'Revenue') * sum(q_A.'Opportunity.Probability') / 100 as 'new_measure'

If instead, you are not able to sum the probability metric you could use average() as your aggregation function. As long as the probability is the same for all rows in your group it should be fine. first() or last() are other functions to consider but just check the behavior before implementing it.

| improve this answer | |

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.