[SalesForce] In Analytics Wave – How to sum values from a related parent object when there are multiple child rows in dataset

In Wave I have a dataset where each row is a CampaignMember, but I go up through object relationships to reach Opportunities via a custom managed lookup on the Account to its first New Business opp: ContactId.AccountId.New_Business_Opportunity__c. As part of my analysis, I need a sum of the won opportunity Amounts, but in many cases there are multiple CampaignMember rows that all relate to the same opportunity, and these multiply the resulting sum.

In native Salesforce reports (if I remember correctly) parent fields are only summed once per parent record, not once per child record. Is there any way to achieve this in a Wave dashboard?

I've already found that I can get accurate totals by grouping my Compare Table by Contact/Lead Id; I use one column to count the number of CampaignMember records for that contact or lead, and then in another formula column I can divide the Amount sum by that count and get an accurate Amount for that contact group. So then I enable "Show Totals" and the total is accurate. However I can't seem to get just that totaled number to display in a number component on my dashboard. And I have to believe there is a better way to get a sum of only unique parent records.

EDIT:

If there is a good way to filter out duplicate rows in a SAQL query (based on any specified field), that would solve this, I could just dedupe on the ContactId.AccountId.New_Business_Opportunity__c column).

Best Answer

So I figured this out eventually... I needed to directly edit the SAQL to create a hidden data stream where the rows are grouped by opportunity ID, and created an identifier to output the sum of Amount divided by the row count for that group, which is effectively the real Amount for that unique opportunity. Then in my result statement I can pull in the sum of the groups' values for an accurate total.

Here is my full step SAQL for anyone interested:

q = load "0Fbi0000000TNCrCAO/0Fci0000000Tcw6CAC";
q_A = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["5 months ago".."1 month ago"];
q_B = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["5 months ago".."1 month ago"];
q_C = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["5 months ago".."1 month ago"] && date('ContactId.AccountId.New_Business_Opportunity__c.CreatedDate_Year', 'ContactId.AccountId.New_Business_Opportunity__c.CreatedDate_Month', 'ContactId.AccountId.New_Business_Opportunity__c.CreatedDate_Day') in ["5 months ago".."current month"] && 'ContactId.AccountId.New_Business_Opportunity__c.IsWon' == "true";
q_D = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in ["5 months ago".."1 month ago"] && date('ContactId.AccountId.New_Business_Opportunity__c.CreatedDate_Year', 'ContactId.AccountId.New_Business_Opportunity__c.CreatedDate_Month', 'ContactId.AccountId.New_Business_Opportunity__c.CreatedDate_Day') in ["5 months ago".."current month"] && 'ContactId.AccountId.New_Business_Opportunity__c.IsWon' == "true";

uniqueopps = group q_D by 'ContactId.AccountId.New_Business_Opportunity__c';

oppsums = foreach uniqueopps generate sum('ContactId.AccountId.New_Business_Opportunity__c.Amount') / count() as 'Amount';

result = group q_A by all full, q_B by all full, q_C by all full, oppsums by all;
result = foreach result generate coalesce(count(q_A), 0) as 'CampaignMembers', coalesce(unique(q_B.'Contact_or_Lead_Id__c'), 0) as 'Contacts&Leads', coalesce(unique(q_C.'ContactId.AccountId.New_Business_Opportunity__c'), 0) as 'Won Opps', coalesce(sum(oppsums.'Amount'), 0) as 'Opp Amount Sum';
result = limit result 2000;
Related Topic