Using Formulas to Create Dynamic Custom Values
When you create a Dynamic Custom Value, you can use formulas to create unique calculations/metrics specific to your business or clients. This custom value will display in the Decision Grid and can be used to display, segment, and report on these unique data points.
When entering your formula, you have access to the arithmetic operators listed in the table below. You can utilize standard metrics in Shape, static or dynamic custom values you've created, and/or numbers to create formulas.
If your formula is valid, a green check box will appear below the formula field and will save when you click Create.
A red warning will appear below the formula field if your formula is invalid. It will not save when you hit Create.
Supported Operators
Operator | Symbol | How it Works |
Opening Parentheses | ( | Used for order of operations. Equations within parentheses will be evaluated first. Must include an opening and closing parentheses |
Closing Parentheses | ) | Used for order of operations. Equations within parentheses will be evaluated first. Must include an opening and closing parentheses |
Addition | + | Adds one element to another. Elements can be metrics, custom values, or numbers. |
Subtraction | - | Subtracts one element from another. Elements can be metrics, custom values, or numbers. |
Multiplication | x | Multiplies one element by another. Elements can be metrics, custom values, or numbers. |
Division | ÷ | Divides one element by another. Elements can be metrics, custom values, or numbers. |
For examples of Custom Value formulas and use cases, please review the article "Custom Value Use Cases."
Using Case Statements to Create Dynamic Custom Values
When you create a dynamic Custom Value, you also have the option to use Case Statements.
Case statements evaluate if a condition is met and return a specific value if it's true. If it's not, it will continue cycling through the conditions until the condition is true and it returns a result. If no conditions are true, it returns the value with the ELSE statement.
Parts of a Case Statement:
CASE: Opens the case statement (required)
WHEN: The condition you want to evaluate. You can have multiple WHEN clauses (required)
THEN: The result that the Custom Value will return if the WHEN clause is true. A THEN clause must follow every WHEN clause (required)
ELSE: The else result is returned if none of the WHEN clauses are true. (required)
END: Closes the case statement (required)
Formatting Case Statements
As part of your case statements, you can use the arithmetic operators above and other operators that check for equality, inequality, or size comparisons.
You can also chain multiple conditions as part of your when clause together. Examples of how these might look are below:
Cast Statement 1
CASE
WHEN condition A THEN Result A
WHEN condition B THEN Result B
WHEN condition C THEN Result C
ELSE Value X
END
Cast Statement 2 (using arithmetic operators)
CASE
WHEN metric 1 = 1000 THEN Result D
WHEN metric 1 != 1000 THEN Result E
ELSE Value X
END
Cast Statement 3 (using mix of all types of operators)
CASE
WHEN metric 1 + metric 2 <1000>THEN Result F
WHEN metric 3 - metric 4 = 1000 THEN Result G
WHEN metric 5 OR metric 6 = 5000 THEN Result H
ELSE Value X
END</1000>
Supported Operators
Opening Parentheses(Used for order of operations. Equations within parentheses will be evaluated first. Must include an opening and closing parentheses
Operator | Symbol | How it Works |
AND | AND | Displays a result if all of the conditions are separated by AND to be true |
OR | OR | Displays a result if any of the conditions separated by OR are true |
Equal | = | Displays a result if the condition is equal to value/metric following |
Not Equal | != | Displays a result if the condition is not equal to value/metric following |
Less Than | < | Displays a result if the condition is less than the value/metric following |
Less Than or Equal to | <= | Displays a result if the condition is less than or equal to the value/metric following |
Greater Than | > | Displays a result if the condition is greater than the value/metric following |
Greater Than or Equal to | >= | Displays a result if the condition is greater than or equal to the value/metric following |
Opening Parentheses | ( | Used for order of operations. Equations within parentheses will be evaluated first. Must include an opening and closing parentheses |
Closing Parentheses | ) | Used for order of operations. Equations within parentheses will be evaluated first. Must include an opening and closing parentheses |
Addition | + | Adds one element to another. Elements can be metrics, custom values, or numbers. |
Subtraction | - | Subtracts one element from another. Elements can be metrics, custom values, or numbers. |
Multiplication | x | Multiplies one element by another. Elements can be metrics, custom values, or numbers. |
Division | ÷ | Divides one element by another. Elements can be metrics, custom values, or numbers. |
For examples of Custom Value formulas and use cases, please review the article "Custom Value Use Cases."