With Analytic Reporting you can now calculate your own functions using field values and/or constants.
Please follow these steps for function creation:
1. Open the report where you would like to add the function
2. Open the Calculated fields tab
3. Click the green “+” to add a formula. You can add an unlimited number of calculated functions.
4. Replace the Untitled field 0 with the name of the new field. In the Edit formula column first choose the operator: Add/Subtract/Multiply/Divide/Add Date/Subtract Date/If.
5. After you have selected the necessary operation type, please select fields or constants on which to operate;
For example, to have the Budget cost reduced by 100 USD for every Campaign choose Subtract, then choose Field and select Budget cost. Select Constant in the next line and input 100. The formula will be displayed in the last column. The example will look like:
To continue the example, to subtract the Actual cost from the Budget cost, change Constant to Field and then select Actual cost:
6. To add additional calculations to the formula, select Sub-calculation in this way it is possible to make as many sub calculations as necessary.
For example, to calculate Campaign Actual Response Percentage (%) from Expected, the formula would need *100 and two field division, so it would look like this:
You can remove any formula with the red x on the right side.
7. In the Detailed Report it is possible to Preview the changes, however, in summary or pivot reports it is better to save the report. It is also possible to average or sum the calculated value under the Aggregates tab:
8.Finally identify under Grouping and Sorting if summaries / grand totals of the newly calculated value are needed:
9. Remember to save the new settings!
Under Calculated fields, you can also use Add Date, Subtract Date and If, which will work a bit differently than Add, Subtract, Multiply, Divide, as they have different calculation rules.
The example below is for If condition.
All If…then…else have nested function capability. If nested functions are necessary, please select sub-calculation.
More than 1 pick-list value can be selected under If…then filters:
To edit the formula script manually, click Edit SQL on the right side.
IMPORTANT: If you edited and saved the custom script, you will no longer be able to edit it via the interface. Added custom scripts can be further edited manually only.
After editing the custom script, remember to save to make your function available for use.
The check-box Override formula allows you to switch between the manually edited script and the original formula (before editing). Keep the check-box selected, if you wish to use the edited formula. Deselecting the check-box will use the original function.
