This blog post is a follow-up to one of my earlier blogs Custom Rollups with SAQL in CRMA which was published last year. Not long after, Salesforce released the Pivot Table Enhancements on the new version of compact form in its Salesforce Winter ’23 update.
Out-Of-The-Box Subtotal
I took a look and this is a great feature that everyone has wanted for a long time. If you have read my last article, you don’t need to write any grand total and subtotal in SAQL nor rename it in XMD to show your Subtotal. Just by clicking the “∑” in the pivot table mode, you have the Show Subtotals function available to you. Take the same example in the old article which demoed how to do Subtotal in Pivot Table in the old way, we still have the dataset with Region, County, Business, and Sales information. If you have Region & County as your Row and Business as your Column, with Column Total, Row Total, and Subtotal enabled, you have something like this:
q = load “Sample_Sheet1”;
p = group p by rollup(‘Business’, ‘Region’, ‘Country’);
p = foreach p generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’, sum(q.’Sales’) as ‘Sales’, grouping(q.’Business’) as ‘grouping_Business’, grouping(q.’Region’) as ‘grouping_Region’, grouping(q.’Country’) as ‘grouping_Country’;
p = filter p by ‘grouping_Business’ == 0 and ‘grouping_Country’ != 0;
p = limit p 2000;
q = group q by rollup(‘Region’, ‘Country’, ‘Business’);
q = foreach q generate q.’Region’ as ‘Region’, q.’Country’ as ‘Country’, q.’Business’ as ‘Business’, sum(q.’Sales’) as ‘Sales’, grouping(q.’Region’) as ‘grouping_Region’, grouping(q.’Country’) as ‘grouping_Country’, grouping(q.’Business’) as ‘grouping_Business’;
s = filter q by ‘grouping_Region’ == 1;
q = filter q by ‘grouping_Region’ == 0;
q = order q by (‘Region’ asc, ‘Country’ asc, ‘Business’ asc);
q = limit q 2000;
q = union q, p, s;
q = limit q 4001;
Adding Window Function – % of Share
It works great on the measure that’s in your dataset. However, if you created a calculated window function, a little orange triangle will pop up from the bottom right saying “Can’t show row totals, column totals, and subtotals, Consider refining your query.”
Don’t be scared. With the built-in Subtotal function, this becomes super easy as well. Now if I want to not only show the Sales, but also % sales within each business for each country, the key will be to separate the windows function from rollup.
Since % sales within each business is a calculation involved with the window function, we put it before all the rollups from the generated code. It will be something like this
p = group q by (‘Business’, ‘Region’, ‘Country’);
p = foreach p generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’
, sum(q.’Sales’) as ‘Sales’
, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’;
Then add this %Share calculation in the foreach generated sentence.
The code with %Share calculation will be like
q = load “Sample_Sheet1”;
p = group q by (‘Business’, ‘Region’, ‘Country’);
p = foreach p generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’
, sum(q.’Sales’) as ‘Sales’
, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’;
p = group p by rollup(‘Business’, ‘Region’, ‘Country’);
p = foreach p generate ‘Business’, ‘Region’, ‘Country’, sum(‘Sales’) as ‘Sales’, sum(‘%Share’) as ‘%Share’, grouping(‘Business’) as ‘grouping_Business’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’;
p = filter p by ‘grouping_Business’ == 0 and ‘grouping_Country’ != 0;
p = limit p 2000;
q = group q by (‘Business’, ‘Region’, ‘Country’);
q = foreach q generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’
, sum(q.’Sales’) as ‘Sales’
, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’;
q = group q by rollup(‘Region’, ‘Country’, ‘Business’);
q = foreach q generate q.’Region’ as ‘Region’, q.’Country’ as ‘Country’, q.’Business’ as ‘Business’
, sum(q.’Sales’) as ‘Sales’
, sum(‘%Sales’)as ‘%Share’
, grouping(q.’Region’) as ‘grouping_Region’, grouping(q.’Country’) as ‘grouping_Country’, grouping(q.’Business’) as ‘grouping_Business’;
s = filter q by ‘grouping_Region’ == 1;
q = filter q by ‘grouping_Region’ == 0;
q = order q by (‘Region’ asc, ‘Country’ asc, ‘Business’ asc);
q = limit q 2000;
q = union q, p, s;
q = limit q 4001;
Issue & Solution with Summing Up the %Share
This calculation will add up the %Share which is the same issue we described in the old article. This is because we have five business categories, so the grand total of %Share is adding up all the five business categories. The logic is correct for the Sum of Sales, it doesn’t provide much value when it comes to %Share.
My thought was, we need to distinguish the total and individual Business Level so CRMA knows it and will perform different calculations for different levels. Let’s first look at these two calculations:
- When at an INDIVIDUAL business level, we want to calculate %Share using the Sales/Total of that business unit. For example, the %Share for Biking in Denmark, Europe will be Sales for Biking in Denmark, Europe/All Biking Sales = 52,856/366,045 = 14.44%
- When at the TOTAL level, we want the %Share to be Total Sales for that country/Worldwide Sales. In this case, All sales in Denmark, Europe/All Sales Worldwide = 199,505/2,713895 = 7.35%
My way to implement this calculation is adding the total calculation at the Business Level as well as the total calculation at the All Level, then using the case statement:
q = group q by (‘Business’, ‘Region’, ‘Country’);
q = foreach q generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’
, sum(q.’Sales’) as ‘Sales’
, sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘Business_Total’
, sum(sum(‘Sales’)) over ([..] partition by all) as ‘World_Total’
, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’;
q = group q by (‘Region’, ‘Country’, ‘Business’);
q = foreach q generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’
, sum(‘Sales’) as ‘Sales’
, sum(‘Business_Total’) as ‘Business_Total’
, max(sum(‘World_Total’)) over ([..] partition by all) as ‘max_n’
, sum(‘%Share’) as ‘%Share’;
–case statement to decide which calculation to use for different level**
q = group q by rollup(‘Region’, ‘Country’, ‘Business’);
q = foreach q generate q.’Region’ as ‘Region’, q.’Country’ as ‘Country’, q.’Business’ as ‘Business’
, sum(q.’Sales’) as ‘Sales’
, sum(‘Sales’)/ case when ‘Business’ in [“Biking”,”Climbing Accessories”,”Cooking Gear”,”Tents”,”Tools”] then sum(‘Business_Total’) else max(‘max_n’) end as ‘%Share’
, grouping(q.’Region’) as ‘grouping_Region’, grouping(q.’Country’) as ‘grouping_Country’, grouping(q.’Business’) as ‘grouping_Business’;
Now everything else stays the same, the Total Level %Share is calculated with the right result.
If your category name might change, you can use binding for the Business category on the safe side.
Ranking
Ranking for the pivot table is not hard. Even though once you switch to the pivot table, you will lose the ordering function through UI, you can still achieve it through SAQL in CRMA, just change
q = order q by (‘Region’ asc, ‘Country’ asc, ‘Business’ asc); to q = order q by (‘Region’ asc, ‘grouping_Country’ asc, ‘Sales’ desc);
‘grouping_Country’ is really important here because it makes sure the Subtotal in Pivot Table is not ordered with all other numbers but stays at the bottom.
Full Code
Lastly, here is the full code for reference
q = load “Sample_Sheet1”;
p = group q by (‘Business’, ‘Region’, ‘Country’);
p = foreach p generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’
, sum(q.’Sales’) as ‘Sales’
, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’;
p = group p by rollup(‘Business’, ‘Region’, ‘Country’);
p = foreach p generate ‘Business’, ‘Region’, ‘Country’, sum(‘Sales’) as ‘Sales’, sum(‘%Share’) as ‘%Share’, grouping(‘Business’) as ‘grouping_Business’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’;
p = filter p by ‘grouping_Business’ == 0 and ‘grouping_Country’ != 0;
p = limit p 2000;
q = group q by (‘Business’, ‘Region’, ‘Country’);
q = foreach q generate q.’Business’ as ‘Business’, q.’Region’ as ‘Region’, q.’Country’ as ‘Country’
, sum(q.’Sales’) as ‘Sales’
, sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘Business_Total’
, sum(sum(‘Sales’)) over ([..] partition by all) as ‘World_Total’
, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’;
q = group q by (‘Region’, ‘Country’, ‘Business’);
q = foreach q generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’
, sum(‘Sales’) as ‘Sales’
, sum(‘Business_Total’) as ‘Business_Total’
, max(sum(‘World_Total’)) over ([..] partition by all) as ‘max_n’
, sum(‘%Share’) as ‘%Share’;
q = group q by rollup(‘Region’, ‘Country’, ‘Business’);
q = foreach q generate q.’Region’ as ‘Region’, q.’Country’ as ‘Country’, q.’Business’ as ‘Business’
, sum(q.’Sales’) as ‘Sales’
, sum(‘Sales’)/ case when ‘Business’ in [“Biking”,”Climbing Accessories”,”Cooking Gear”,”Tents”,”Tools”] then sum(‘Business_Total’) else max(‘max_n’) end as ‘%Share’
, grouping(q.’Region’) as ‘grouping_Region’, grouping(q.’Country’) as ‘grouping_Country’, grouping(q.’Business’) as ‘grouping_Business’;
s = filter q by ‘grouping_Region’ == 1;
q = filter q by ‘grouping_Region’ == 0;
q = order q by (‘Region’ asc, ‘grouping_Country’ asc, ‘Sales’ desc);
q = limit q 2000;
q = union q, p, s;
q = limit q 4001;
Hope this is helpful. Enjoy SAQL in CRMA!
