Purpose
Perform advanced mathematical calculations using operators and functions.
Category Location: All, Calculate or manipulate
- Write calculations to: enter the attribute to contain the resulting value.
- Calculation: construct your equation. To use attributes in the calculation type the @ symbol in the calculation window and then select the desired attribute from the pick list. The attribute will be enclosed in curly brackets { }. Add any other numbers, operators, or functions where desired but make sure any additions are located outside of the curly brackets.
Notes:
- You cannot type curly brackets and the attribute name in the calculation window. You must type the @ and select the attribute from the picklist.
- Attributes with a null value will be treated as having a value of zero.
- Equation syntax follows typical conventions used by Excel and Google Sheets. For complicated formulas, it may be helpful to work out the syntax in Excel or Google Sheets before building the equation in Openprise.
- Only real or whole attribute types are permitted in the equation.
- A calculation result from a previous Action may be used in a subsequent action.
- No validation is performed on the equation, so please test your equation(s) by running the job on a sample of records.
- Job output will contain an attribute, OP_advanced_math_task_info, with the success or failure of the task execution.
- When building your equation, make sure to verify the precision level is appropriate for your calculations. The precision level default is 0.01, and can be configured in the Advanced Configurations section.
Advanced Configuration:
- Precision level: select precision level from the dropdown
- Rounding Options: Standard rounding, always round up or always round down
- Convert to percentage value: check the box to convert number to percentage
Supported operators
Addition: 2 + 2
Subtraction: 2 - 2
Multiplication: 2 * 2
Division: 2 / 2
Exponential: 2 ^ 2
Unary Minus or Plus (Sign Operators): +2 - (-2)
Modulo: 2 % 2
Supported functions
abs: absolute value
acos: arc cosine
asin: arc sine
atan: arc tangent
cbrt: cubic root
ceil: nearest upper integer
cos: cosine
cosh: hyperbolic cosine
exp: euler's number raised to the power (e^x)
floor: nearest lower integer
log: logarithmus naturalis (base e)
sin: sine
sinh: hyperbolic sine
sqrt: square root
tan: tangent
tanh: hyperbolic tangent
Examples
- Calculate time decay using the distance of an activity from conversion. The formula used is Y = 2^(-x/7) where x is the number of days prior to the conversion event. This equation halves an activity's contribution towards attribution every 7 days. In the example below, x is represented by an attribute named Days before.
- Calculate compound interest rate using the formula A = P(1 + r/n)^nt. Where: A stands for the final amount; P is the original principal; r is the interest rate; n is the number of times interest is applied per time period; t is the number of time periods elapsed.
In the example below, the formula is calculated using multiple actions for clarity, but could also have been expressed in one action.
Correcting the formula when attributes have been removed
If a formula involves attributes that have been mistakenly removed from the job, an error will be displayed, BUT the missing attribute will still show in the formula. This is done so you know what the original formula looks like. To correct the formula, please use these steps:
- Navigate to the task using the Advanced Math template. Note the attributes shown in the error message.
- Recreate the missing attribute OR add the missing attribute if it came from the original data source or an upstream job.
- Remove the missing attribute(s), including the curly brackets.
- Using the @ convention, add the missing attribute back to the task.
- Save the task.
- After saving, the error should be cleared.
- Run the job and check the output to validate that the equation has been properly reconstructed.