0

I have a SAQL Query which takes a list and attempts to display a total row at the bottom.

However, the way that my code is written is causing the Total column to be alphabetically rearranged so that it falls within the other fields. The data is correct, it's just re-ordered.


q = load "Opportunity_Campaign_Influence";

mainData = group q by ('CampaignId.Type', 'Opp_Probability');
mainData = foreach mainData generate q.'CampaignId.Type' as 'CampaignId.Type', q.'Opp_Probability' as 'Opp_Probability', sum(q.'ARR_Share__c') as 'A';
mainData = order mainData by ('CampaignId.Type' asc, 'Opp_Probability' asc);
 
totalRow = group mainData by ( 'Opp_Probability');
totalRow = foreach totalRow generate "TOTAL" as 'CampaignId.Type', 
    mainData.'Opp_Probability' as 'Opp_Probability' ,
    sum(mainData.'A') as 'A';

result = union mainData, totalRow;
result = group result by ('CampaignId.Type', 'Opp_Probability');
result = foreach result generate 'CampaignId.Type' as 'CampaignId.Type', 
    'Opp_Probability' as 'Opp_Probability' ,
    sum('A') as 'A';

result = limit result 2000;
0

In case anyone is looking for the answer, I figured it out as well as adding row totals.


q = load "Opportunity_Campaign_Influence";

--Sum the ARR for each campaign type sub grouped by Probability
mainData = group q by ('CampaignId.Type', 'Opp_Probability');
mainData = foreach mainData generate 
    q.'CampaignId.Type' as 'Campaign', 
    q.'Opp_Probability' as 'Probability', 
    sum(q.'ARR_Share__c') as 'ARR';

--Add the row totals to each row of the main data table    
--Use group by rollup to get row totals as well as a grand total
--relabel the grand total Campaign to TOTAL for matching with the union
mainDataWithRowTotal = group mainData by rollup ('Campaign', 'Probability');
mainDataWithRowTotal = foreach mainDataWithRowTotal generate 
    (case when grouping(mainData.'Campaign') == 1
        then "TOTAL"
        else mainData.'Campaign'
        end
    ) as 'Campaign', 
    (case when grouping(mainData.'Probability') == 1 
        then "Total"
        else mainData.'Probability'
        end
    ) as 'Probability', 
--this line isn't working, supposed to replace the blanks with 0    
coalesce(sum(mainData.'ARR'), 0) as 'ARR';

--Calculate the total row, label the campaign TOTAL
--So that it matches with the mainData in the Union
totalRow = group mainData by ('Probability');
totalRow = foreach totalRow generate 
    "TOTAL" as 'Campaign', 
    mainData.'Probability' as 'Probability' ,
    sum(mainData.'ARR') as 'ARR';
    
totalRow = group totalRow by ('Campaign', 'Probability');
totalRow = foreach totalRow generate 
    'Campaign' as 'Campaign', 
    'Probability' as 'Probability' ,
    sum('ARR') as 'ARR';    

result = union mainDataWithRowTotal,totalRow;

result = limit result 2000;

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.