Creating a Simple Calculation
A simple calculation involves using a formula to arrive at a value using one of the fields in an Analytics Report. In the example shown below, a calculation is performed by using a formula to compute the difference between a standard value and the value in a report field.
Locate the Analytics Report that needs the calculation in the Report Browse screen, and click on its icon to display the Data view of the report.
1 |
Create Calculate Field icon |
Click on the icon to display a Calculate Field window. |
2 |
Formula Type list |
Ensure Simple is selected in the list. |
3 |
Calculate Field Name field |
Enter the name you want displayed in the report for the calculated field. |
4 |
Formula Function buttons |
Use to build a formula in the Formula Builder. Function buttons include the Count, Distinct Count, Sum, Average, Minimum, and Maximum. |
5 |
Select Field list |
Use a field in the list as a basis for the formula. The field can be used in conjunction with a function or operand, or in conjunction with a user-defined value and a function or operand. |
6 |
User-Defined field and + Add button |
Enter a value in the blank field and then click the + Add button to add a user-defined value to the formula. |
7 |
Operand buttons |
The operand buttons are enabled when (1) a metric field is selected in the Select Field list or (2) a value has been entered in the User-Defined field and the + Add button is clicked. |
8 |
Case button |
Click if you want to create an If...then condition for the calculation. Once the Case button is selected, the When, End, and Else buttons are enabled. See Simple Calculation Functions & Operands for additional information on setting up a condition. |
Based on selections you make in the Calculated Field window, the Function and Operand buttons are automatically enabled if they can be used in the formula. Similarly, they are disabled if they cannot be used in the formula you are building.
In the calculation below, we are subtracting the value in the Payment Paid Amount from the standard monthly payment, which is $600. When the payment is less than $600, this signifies that the customer has taken advantage of the Discount term of the contract by paying the monthly amount early.
9 |
User-Defined field |
Enter "600" in the field since this is the standard monthly payment in our example. |
10 |
+ Add button |
Click to add the standard monthly payment to the Formula Builder. |
11 |
Operand buttons |
Click on a button to add an operand to the formula. In this example, we select the button since we are going to subtract the value in the field we will use in the formula from a standard "600" dollar value. The subtraction symbol is automatically added to the formula. Make sure your cursor is positioned in the right location in the Formula Builder before inserting an operand. |
12 |
Select Field list |
Choose the field that you want to use in the formula. In this example, we select Payment Paid Amount since this is the value we want to subtract from the standard "600" dollar value. |
13 |
Formula Builder field |
Verify that the formula is correct. In the this example, the formula will calculate the difference between the standard monthly payment of $600 and the amount the customer paid, which will vary each month based on any discounts taken for early payment. |
14 |
Validate button |
Click to check the validity of your formula. |
15 |
Validation message |
Verify that "SQL is valid" displays, indicating your formula is valid. |
16 |
Save button |
Click to save your information and create the simple calculation. |
17 |
Calculated Fields icon |
Click on the icon to display the calculated field. The calculated field will contain the icon if it is a dimension (text-based) field, or the icon if it is a metric (date or number) field. |
18 |
Calculated field in Available Fields list |
To add the field to the report, click on it, and then drag-and-drop it to the Report Builder canvas. |
19 |
Calculated field in Report Builder |
Verify that the calculated field is displayed in the report. You can move the calculated field by clicking on the header, and dragging and dropping the field to the desired location in the report. |