Using Advanced Expression Piping within your forms you can create mathematical formulas in order to calculate a value. The most common formula that can be used is the SUM function. This article will demonstrate how to configure a SUM expression using AEP within a form.
In this Article...
The SUM function will sum a list of values from a number of given fields and is the most commonly used function within expression piping. It can be used to sum a simple list of variables (q1+q2+q3) or can be used for more complicated equations such as multiplying the sum of two parts together ( (q1+q2)*(q3+q4) ).
- Single Operator within an Expression
- Multiple Operators within an Expression
- Finding the SUM without an Operator
The setup and formatting of the SUM expression is imperative in preventing an error message from appearing on your form where the sum of your expression would normally appear. The SUM expression can be set up as:
{{ SUM(variable1+variable2) }} |
OR
{{ SUM(variable1,variable2) }} |
- Each expression is always contained within a pair of double braces {{ }}
- There should be a space between the first instance of SUM and the last bracket
- Variables you are using are case sensitive for the spelling of the Identifier
- Always close each SUM operation with brackets ( )
Single Operator within an Expression
This setup will work using the single operator (+) for a list of fields you wish to sum together. An example of using this setup would be when you are looking to sum a list of items within a table to get a total.
TIP! When using a single operator, the SUM function is not required. For example, the following expressions will generate the same result.
{{ variable1+variable2+variable3+variable4 }}
OR
{{ SUM(variable1+variable2+variable3+variable4) }}
OR
{{ SUM(variable1,variable2,variable3,variable4) }}
Multiple Operators within an Expression
This is where it will get more complicated as this is dependant on how many operations you wish to perform as well as in what order should those operations occur in. The order of operations follows the BEDMAS operation (Brackets, Exponents, Division/Multiplication, Addition/Subtraction) from left to right.
Example: {{ SUM((variable1*variable2)+variable3) }}
Finding the SUM without an Operator
The SUM function can also be used without adding any operators to the expression. For example, to find the sum of a column you can simply use the column identifier. Below are examples of formulas without operators.
Sum of a Column | {{ SUM(q1[:0]) }} |
Based on Grid Indexing, this will find the sum of column 1. |
Sum of a Row | {{ SUM(q2[1:]) }} |
Based on Grid Indexing, this will find the sum of Row 2. |
Sum of a Range | {{ SUM(q1[0:4]) }} |
Based on Grid Indexing, this will give you the sum of the first 4 cells. |
Sum of Reviewer Scores | {{ SUM(q1.score,q2.score,q3.score) }} |
The score given to each of the 3 questions will be added to the sum. NOTE: In this example, three questions have been given identifiers of q1, q2, and q3. These questions exist within the same form. |
TIP! Remember that the first row in a table is referred to as row 0 as it generally holds column headers. Looking at the sum of a row example above, if we were looking to calculate row 3, the expression would appear as {{ SUM(q2[2:] }}.
{{ SUM(Q1[:0]) }} |
{{ SUM(Q1[0:]) }} |
{{ SUM(Q1[0:5]) }} |
The example calculation walks through a scenario where we are trying to calculate the budget the applicant requires to purchase certain items. The budget itself is based on multiple variables that look for the number of units the applicant requires multiplied by the cost of each unit. While we can have individual expressions for each line, we could use the total values from the individual expression to get our grand total without piping the responses to a new page. We can however, use a multiple SUM function within our expression to get the total.
For this example, there are three rows of different items (Computers, Chairs, and Desks) that have two columns to determine the number of items they are reporting on and how much each item would cost.
Item |
Number of Units |
Cost |
Computers |
- |
- |
Chairs |
- |
- |
Desks |
- |
- |
Using this example, the expression to get the grant total would be:
{{ SUM((q1[0]*q1[1])+(q1[2]*q1[3])+(q1[4]*q1[5])) }}
Within this expression, the even identifiers (0,2 and 4) are pulling the number of items the user is asking for and the odd value identifiers (1,3 and 5) are pulling the value of how much each one costs.
For more information on how row and column indexing is done in grid questions, you can refer to our article on AEP Grid Indexing. You can also refer to the form below displaying the index of each cell.
Item | Number of Units | Cost |
Computers | q1[0] | q1[1] |
Chairs | q1[2] | q1[3] |
Desks | q1[4] | q1[5] |
Let’s say John Smith is requesting computers, desks and chairs. Computers have been valued at $500 each Chairs at $50 each and desks at $200 each. John then states he needs 10 computers, 10 chairs and 5 desks. With the equation shown above each part calculates to $5000, $500 and $1000, respectively, to then calculate the grand total of $6500. Additionally, you may wish to determine the taxes applied to the total. In order to do so you can multiple the piped variables by a fixed value, such as 1.13.
Item |
Number of Units |
Cost |
Computers |
10 |
500 |
Chairs |
10 |
50 |
Desks |
5 |
200 |
To calculate the total plus tax, your expression should be setup as follows:
{{ (SUM((q1[0]*q1[1])+(q1[2]*q1[3])+(q1[4]*q1[5])))*1.13 }}
Here is the breakdown of each line within the expression above:
Line 1 performs the operations within each of the brackets
(10*500)
(10*50)
(5*200)
Line 2 then performs the SUM operation of the previous line together.
SUM(5000+500+1000)
Line 3 then performs the final operation of multiplying the previous line by the taxed amount.
(6500*1.13)
The expression provides us with a response of 7345 as the total cost.