Salesforce CPQ Complex Summary Variables

Spencer Kunze |
 Revenue Cloud |
 Jun 17, 2020

Salesforce CPQ Complex Summary Variables
How to use a summary field to multiply multiple records (with examples)

Roycon has been operating in the Salesforce CPQ world, delivering comprehensive solutions and adaptions to our clients that require a robust solution for their business needs. Salesforce CPQ is an absolute workhorse that can handle multiple pricebooks, quoting methods, markup and discount ability, contract/order auto creation, and much much more. We recently tackled an obstacle when helping a client transition from using a complex spreadsheet (with layered cell references and calculations) to using the Salesforce CPQ via Quote Configurator and the Quote Line Editor. The heavy lifting was done by utilizing Summary Variables which is what we’ll focus on here.

Summary Variables are extremely useful but lack certain abilities. I recently was attempting to multiply the sum of a field by a number then add another field to it (example 2 below) and was unable to find any resource. Hopefully this will provide a solution and maybe even inspiration for a creative solution.

What is a Summary Variable?

“Summarize number field information about products into a single object called a summary variable. You can reference a summary variable in price conditions, price actions, product rule error conditions, quote term conditions, and other summary variables.”

Summary variables shine when either referencing one field and multiplying/dividing/adding subtracting a value e.g.
Product A quantity field * 4
Product B quantity field + 1

Or a bit more complex – stacking single fields by using multiple summary variables
e.g. Product A quantity field + Product B quantity field + Product C quantity field.

But what happens when that list is more than a dozen? When that list is constantly evolving? A simple approach would require the “chained” Summary Variables to constantly be remade for new/discontinued Products.

What happens when you try to multiply a sum then add another number to it?
E.g. ((Product A quantity field + Product B quantity field + Product C) * 4) + Product D quantity field. The result of that formula would set the quantity for what we’ll call “Product E” when I am in the Salesforce CPQ Quote Line Editor (QLE).
The Summary Variable is not able to have a “stacked” sum AND multiply. It’s one or the other.

I needed a Summary Variable (and well as Product Rule and Price Rule for full implementation) to allow me to handle these more complex numbers. Here’s what I did that allowed both examples above to function with flexibility and complexity.

Example 1 – stacked Summary Variables (Product A quantity field + Product B quantity field + Product C quantity field+…)
I created a checkbox field on the Product object called “Uses Sum of A to Z” and added it to the page layout.
For Products A to Z (26) I navigated to the Product record and checked that box.
Then I created a Summary Variable as follows:
Target Object = Quote Line
Aggregate Function = Sum (to take the sum of what field is defined in Aggregate Field)
Aggregate Field = Quantity (to sum the Quantity field on the quote line)
Filter Field = Uses_Product_A_to_Z__c (the API name of the checkbox field. I added this manually by going to Setup->Summary Variable object->Filter Field field->adding API name to picklist)
Operator = equals
Filter Value = TRUE (to filter checked boxes)
This allows me to check boxes on new Products, and uncheck boxes on old discontinued Products. It doesn’t rely on Product Code or Product Family. It’s easy to filter, add to and allows the ability to multiply as shown in the next example.

Example 2 – ((Product A quantity field + Product B quantity field + Product C quantity field ) * 4) + Product D quantity field
I created a checkbox field on the Product object called “Uses Product E” and added it to the page layout.
For Product A,B, and C I navigated to the Product record and checked that box.
I then created a Summary Variable as follows:
Target Object = Quote Line
Aggregate Function = Sum (to take the sum of what field is defined in Aggregate Field)
Aggregate Field = Quantity (to sum the Quantity field on the quote line)
Filter Field = Uses_Product_E__c (the API name of the checkbox field. I added this manually by going to Setup->Summary Variable object->Filter Field field->adding API name to picklist)
Operator = equals
Filter Value = TRUE (to filter checked boxes)
Composite Operator = Multiply
Variable Element = null
Value Element = 4

This provided me the ability to Sum one field on multiple records and Multiply with just one Summary Variable. To complete the job I needed one more Summary Variable to add in Product D which gives us a final product of:
((Product A quantity field + Product B quantity field + Product C) * 4) + Product D quantity field

*Remember that the Summary Variable Filter Field is a picklist field and that you will manually need to navigate to it in setup and add the API name to the picklist.*

The approach to both these examples reduced the number of overall Summary Variables significantly. Less variables mean less chance of something going wrong – a typo, a wrongly selected Aggregate Function, a missed Operator, etc. In the future I’ll be able to modify these Summary Variables much quicker and easily than editing 20+ Summary Variables and creating who knows how many.

While this is simply scratching the surface of Salesforce CPQ, we hope this has clarified Summary Variables and given you an advantage with this complex and powerful ecosystem.

For more information about Salesforce CPQ or working with Summary Variable fields in CPQ, feel free to reach out to us here.

Spencer Kunze

Spencer Kunze

Salesforce App Builder

Spencer is a brilliant app builder who had a front-row seat to the family small business so whenever a company can use Salesforce to make their lives easier, Spencer personally knows just how big an impact that can have.

Related Articles

What is Salesforce Revenue Cloud

What is Salesforce Revenue Cloud

No matter how great the products and services we sell may be, the biggest driving factor to customer satisfaction is a pleasant customer experience. Approximately 80% of customer LTV comes after the first order. And over 2/3 of deals fall through at the quoting stage....

read more
Learn Salesforce CPQ: How to resize columns in QLE

Learn Salesforce CPQ: How to resize columns in QLE

Salesforce CPQ: How to Resize Columns in QLE Today we will be covering a Salesforce CPQ topic. How to resize columns in QLE. This is a new feature from the Winter 2021 release. The default setting in CPQ does not allow column resizing. So, let's turn that on first. 1....

read more
How I passed the Salesforce CPQ Certification

How I passed the Salesforce CPQ Certification

My Journey to Pass the Salesforce CPQ Certification My journey with Salesforce started a little over a year ago. I have spent most of my career in the IT “world” and when I found out about Salesforce and what the ecosystem can do, I was immediately interested. I know...

read more
Tweet
Share