CRM Analytics has several out-of-the-box options for visualizing data in tables, including Value Tables, Compare Tables, and Pivot Tables. In our experience working with Pivot Tables, we’ve seen a common request to perform not just the Grand total, but also the Subtotals for each group.
In this blog post, we’ll be walking you through the steps to create custom rollups in a pivot table in CRM Analytics, using custom SAQL code.
For the demo purpose, I created a dataset with Region, Country, Business, and Sales information.
Understand Out-Of-The-Box Option for Totals
The shortcut is to have the basic structure built first in the user interface instead of switching to SAQL directly. Here I have my Region, Country, and Business as the dimension and Sales as the measure in the Pivot Table. By clicking the ∑ sign I can quickly add Show Column Totals and Row Totals.
The table can be divided into three parts:
- the column total – e.g. 336,045 for Biking
- the body of the table with row total
- the grand total – 2,713,895
Let’s see how the SAQL works to have the above three sections put together.
Part 1: the column total
q = load “Sample_Sheet1”;
grand_total = group q by ‘Business’;
grand_total = foreach grand_total generate ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, 0 as ‘grouping_Business’;
In this example we want the column total to be at the business level. The query generates the Business as the dimension and Sales as the measure. 0 as ‘grouping_Business’ can not be omitted here; it sets up the aggregation level for Business.
Part 2: the body of the table with row total
main = group q by rollup(‘Region’, ‘Country’, ‘Business’);
main = foreach main generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’, grouping(‘Business’) as ‘grouping_Business’;
main = filter main by ‘grouping_Region’ == 0 and ‘grouping_Country’ == 0;
main = order main by (‘Region’ asc nulls last, ‘Country’ asc nulls last, ‘Business’ asc nulls last);
Here it uses rollup and grouping to display aggregations of grouped data at Region, Country, and Business level and runs sum(‘Sales’). Note – the sequence matters. In the pivot table, the last grouped dimension will be on the column.
Next, we filter main by ‘grouping_Region’ == 0 and ‘grouping_Country’ == 0. This means we don’t want the rollup at the region or country level, but only show the data with the rollup at the Business level.
Part 3: the grand total
subtotal = filter main by ‘grouping_Region’ == 1 and ‘grouping_Country’ == 1 and ‘grouping_Business’ == 1;
Now the last piece to add will be the grand total level data. In order to do this, we filter the grouping for Region and Country and Business all equals 1 which means the row is the grand total.
Last, we union all the steams to generate this pivot table:
main = union main, grand_total, subtotal;
Full code for reference:
q = load “Sample_Sheet1”;
grand_total = group q by ‘Business’;
grand_total = foreach grand_total generate ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, 0 as ‘grouping_Business’;
main = group q by rollup(‘Region’, ‘Country’, ‘Business’);
main = foreach main generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’, grouping(‘Business’) as ‘grouping_Business’;
subtotal = filter main by ‘grouping_Region’ == 1 and ‘grouping_Country’ == 1 and ‘grouping_Business’ == 1;
main = filter main by ‘grouping_Region’ == 0 and ‘grouping_Country’ == 0;
main = order main by (‘Region’ asc nulls last, ‘Country’ asc nulls last, ‘Business’ asc nulls last);
main = union main, grand_total, subtotal;
Adding subtotal
Now think about at what level we want to add a subtotal — Region, Country, or Business level? In this case, it is at the Country level. The user interface has no Subtotal function, in order to add a subtotal, we can add a separate code stream to create a placeholder called “zzz_Subtotal” in the country field.
The reason we name it zzz_Subtotal is because when the order is set to be in alphabetical order, the field with the name starting with “zzz” will most likely be at the end.
q = load “Sample_Sheet1”;
grand_total = group q by ‘Business’;
grand_total = foreach grand_total generate ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, 0 as ‘grouping_Business’;
main = group q by (‘Region’, ‘Country’, ‘Business’);
main = foreach main generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’, sum(‘Sales’) as ‘Sales’;
sub_total = group q by (‘Region’, ‘Business’);
sub_total = foreach sub_total generate ‘Region’ as ‘Region’, “zzz_Subtotal” as ‘Country’, ‘Business’ as ‘Business’, sum(‘Sales’) as ‘Sales’;
main = union main, sub_total;
This created the zzz_Subtotal at the very end of the table in the country field.
The rest is the same as before, creating the body with rollup and grouping.
main1 = group main by rollup(‘Region’, ‘Country’, ‘Business’);
main1 = foreach main1 generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’, grouping(‘Business’) as ‘grouping_Business’;
subtotal = filter main1 by ‘grouping_Region’ == 1 and ‘grouping_Country’ == 1 and ‘grouping_Business’ == 1;
main = filter main1 by ‘grouping_Region’ == 0 and ‘grouping_Country’ == 0;
main = order main by (‘Region’ asc nulls last, ‘Country’ asc nulls last, ‘Business’ asc nulls last);
result = union main, grand_total, subtotal;
Here we ended up with a pivot table that consists of the Subtotal.
Update the name to subtotal
No one likes to see zzz_Subtotal in the field. If you rename it to Subtotal, it will show up right before Sweden instead of at the end. We can use XMD to update the field with the name ‘Subtotal’ but with an API name as zzz_Subtotal. An easy way to do it is to go find the Dataset, Click Edit Values on Country.
You can randomly select a field and click the pencil icon to change the name to zzz_Subtotal (don’t worry, we will update it in json code later). For this case, I renamed the United Kingdom.
Once I rename it, it will pop to the very top:
Save your change.
Now go back to your dataset and right-click on Edit and go to the Dataset overview page where you download the json file.
Locate to the zzz_Subtotal part:
change the “zzz_Subtotal” in “label” to “Subtotal” and “United Kingdom” in “member” to “zzz_Subtotal”.
Once saved, go back to the dataset page and select replace to find your updated json to upload.
Now when you refresh the dashboard, the zzz_Subtotal is successfully replaced with Subtotal as the field name.
Fix the grand total issue
This is not done yet. If you look closely enough, you will notice that the grand total amount is not correct. The subtotal of Europe and North America should add up to 2,713,895 (1,822,286 + 891,609) instead of 5,427,790. The Grand total is exactly twice of 2,713,895. Wondering what is happening here?
This is because the system treats Subtotal as an independent country we created in the Country field. In this way, the Total will be the sum of all the countries plus the subtotal country, which is double counting the number.
Since I know it will always be double counting the data, I want to divide the Total by 2. The way I am doing this is to duplicate the main1 stream and rename it as main2. Then divide the sum(‘Sales’) by 2, and use subtotal to filter main2 stream:
main2 = group main by rollup(‘Region’, ‘Country’, ‘Business’);
main2 = foreach main2 generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’, sum(‘Sales’)/2 as ‘sum_Sales’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’, grouping(‘Business’) as ‘grouping_Business’;
subtotal = filter main2 by ‘grouping_Region’ == 1 and ‘grouping_Country’ == 1 and ‘grouping_Business’ == 1;
In this way when I put everything together, eventually the subtotal will be the correct amount.
Adding market share calculation
We have the Subtotal & Total for the Sales, isn’t this exciting?! Now we might want to take the momentum and add some other measures such as Quantities, Budgets, and so on. But when it comes to the share of the market, there might be some issues.
Let’s just copy the logic and apply it to the market share calculation.
In order to calculate the market base, we need the window function for the market total for both the Country level and the Region Level. So in addition to the sum(‘Sales’), we add the share calculation in the code:
sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’
Ofcourse in main2 when we divide the sum(‘sum_Sales’) by 2, the %Share needs to be divided by 2 as well.
The result is as below
The %Share calculation on the Business level is calculated, but on the Total level it is 500% – what is that?! Looking deeper we figured out that this is the sum of all 5 business categories which follow the same logic as that of the Total for the Sum of Sales. But the logic is correct for the Sum of Sales, it doesn’t provide much value when it comes to %Share. How could we fix it?
My thought was we need to distinguish the total and individual business levels, so CRMA knows it will perform this calculation when at an individual business level and a different calculation when it is at the total level. Let’s first come up with 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%.
Once this is done, next we need to think of how to define these two different levels of calculations, so CRMA can know when to perform which calculation.
I haven’t figured out a better way, so if you do please let me know! What I did was call out those values on an individual level. When the value matches with “Biking”,”Climbing Accessories”,”Cooking Gear”,”Tents”,”Tools” in this case, perform an individual level calculation, or execute the Total level calculation.
sum(‘sum_Sales’)/ case when ‘Business’ in [“Biking”,”Climbing Accessories”,”Cooking Gear”,”Tents”,”Tools”] then sum(‘Business_Total’) else max(‘max_n’) end as ‘%Share’
This is the final result.
Full code for reference
q = load “Sample_Sheet1”;
–grand_total stay untouched
grand_total = group q by ‘Business’;
grand_total = foreach grand_total generate ‘Business’ as ‘Business’, sum(‘Sales’) as ‘sum_Sales’, sum(‘Sales’) / sum(sum(‘Sales’)) over ([..] partition by ‘Business’) as ‘%Share’, 0 as ‘grouping_Business’;
–adding both individual business total and world total
main = group q by (‘Region’, ‘Country’, ‘Business’);
main = foreach main generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’
, sum(‘Sales’) as ‘sum_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’;
main = group main by (‘Region’, ‘Country’, ‘Business’);
main = foreach main generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’
, sum(‘sum_Sales’) as ‘sum_Sales’
, sum(‘Business_Total’) as ‘Business_Total’
, max(sum(‘World_Total’)) over ([..] partition by all) as ‘max_n’
, sum(‘%Share’) as ‘%Share’;
sub_total = group q by (‘Region’, ‘Business’);
sub_total = foreach sub_total generate ‘Region’ as ‘Region’, “zzz_Subtotal” as ‘Country’, ‘Business’ as ‘Business’
, sum(‘Sales’) as ‘sum_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’;
sub_total = group sub_total by (‘Region’, ‘Business’);
sub_total = foreach sub_total generate ‘Region’, ‘Country’, ‘Business’
, sum(‘sum_Sales’) as ‘sum_Sales’
, sum(‘Business_Total’) as ‘Business_Total’
, max(sum(‘World_Total’)) over ([..] partition by all) as ‘max_n’
, sum(‘%Share’) as ‘%Share’;
main = union main, sub_total;
–case statement to decide which calculation to use for different level
main1 = group main by rollup(‘Region’, ‘Country’, ‘Business’);
main1 = foreach main1 generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’
, sum(‘sum_Sales’) as ‘sum_Sales’
, sum(‘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(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’, grouping(‘Business’) as ‘grouping_Business’;
main2 = group main by rollup(‘Region’, ‘Country’, ‘Business’);
main2 = foreach main2 generate ‘Region’ as ‘Region’, ‘Country’ as ‘Country’, ‘Business’ as ‘Business’, sum(‘sum_Sales’)/2 as ‘sum_Sales’, 1 as ‘%Share’, grouping(‘Region’) as ‘grouping_Region’, grouping(‘Country’) as ‘grouping_Country’, grouping(‘Business’) as ‘grouping_Business’;
subtotal = filter main2 by ‘grouping_Region’ == 1 and ‘grouping_Country’ == 1 and ‘grouping_Business’ == 1;
main = filter main1 by ‘grouping_Region’ == 0 and ‘grouping_Country’ == 0;
main = order main by (‘Region’ asc nulls last, ‘Country’ asc nulls last, ‘Business’ asc nulls last);
result = union main, grand_total, subtotal;
Hope this is helpful for you. Enjoy SAQL!
**While I was drafting this blog post, Salesforce announced the Beta version for Subtotals in Pivot Tables in their Salesforce Winter ’23 Release Notes. However, I hope this article is still helpful in understanding how pivot tables work in Salesforce and calculating the market share.
