Formulas reference
This reference provides detailed information about the formulas that you can use in your formula fields.
Expression syntax
Formula expressions use a syntax similar to that used by calculators and other mathematical applications.
Operations
Formula and rollup expressions support standard mathematical operations using either operator notation or function notation. Here are the most common operations:
- Operator notation
- Function notation
| Operation | Description | Operator | Example expression | Result |
|---|---|---|---|---|
| Addition | Add two values | + | 2 + 3 | 5 |
| Subtraction | Subtract second value from first | - | 5 - 2 | 3 |
| Multiplication | Multiply two values | * | 4 * 3 | 12 |
| Division | Divide first value by second | / | 10 / 2 | 5 |
| Operation | Description | Function | Example expression | Result |
|---|---|---|---|---|
| Addition | Add two or more values | add(x, y, ...) | add(2, 3) | 5 |
| Subtraction | Subtract second value from first | subtract(x, y) | subtract(5, 2) | 3 |
| Multiplication | Multiply two or more values | multiply(x, y, ...) | multiply(4, 3) | 12 |
| Division | Divide first value by second | divide(x, y) | divide(10, 2) | 5 |
Order of operations
- Operator notation
- Function notation
When using operator notation, you can use parentheses to override the default order of operations:
2 + 3 * 4 // = 14 (multiplication happens first)
(2 + 3) * 4 // = 20 (addition happens first)
When using function notation, the order of operations is determined by functional composition:
add(2, multiply(3, 4)) // = 14 (multiplication happens first)
multiply(4, add(2, 3)) // = 20 (addition happens first)
Variables
Formula expressions can reference some Jira fields as variables. For example, the following formula expression calculates the estimated cost of labour for each row by multiplying the number of hours worked by an average labour cost ($50/hour) defined in the expression:
- Operator notation
- Function notation
// Formula expression to calculate the estimated cost of labour
// 'timespent' is the field key for Jira's 'Time Spent' field
timespent * 50
// Formula expression to calculate the estimated cost of labour
// 'timespent' is the field key for Jira's 'Time Spent' field
multiply(timespent, 50)
Formula expressions can also reference other formula fields as variables. For example, the following formula expression calculates the estimated cost of labour per Story Point for each row by dividing the estimated cost of labour by the number of Story Points:
- Operator notation
- Function notation
// Formula expression to calculate the estimated cost of labour per Story Point
// 'formula_labour_cost' is the field key for the formula field created earlier
// 'customfield_10028' is the field key for Jira's 'Story Points' field in this particular Jira instance
formula_labour_cost / customfield_10028
// Formula expression to calculate the estimated cost of labour per Story Point
// 'formula_labour_cost' is the field key for the formula field created earlier
// 'customfield_10028' is the field key for Jira's 'Story Points' field in this particular Jira instance
divide(formula_labour_cost, customfield_10028)
Formula fields are evaluated in descending order. When referencing other formula fields in a formula expression, make sure the referenced formula fields appear above the current formula field in the list. You may need to reorder your formula fields to ensure the formula expression is evaluated correctly.
Rollup expressions work similarly, but they are evaluated using summary level data instead of row-level data. To reference a variable in a rollup expression, append an aggregation method to the variable name (e.g. .sum, .avg, .min, .max). This determines how the referenced field's values will be summarized when the rollup expression is evaluated. For example, the following rollup expression calculates the estimated cost of labour per Story Point for a group of rows by dividing the total estimated cost of labour by the total number of Story Points:
- Operator notation
- Function notation
// Rollup expression to calculate the estimated cost of labour per Story Point
// 'formula_labour_cost' is the field key for the formula field created earlier
// 'customfield_10028' is the field key for Jira's 'Story Points' field in this particular Jira instance
formula_labour_cost.sum / customfield_10028.sum
// Rollup expression to calculate the estimated cost of labour per Story Point
// 'formula_labour_cost' is the field key for the formula field created earlier
// 'customfield_10028' is the field key for Jira's 'Story Points' field in this particular Jira instance
divide(formula_labour_cost.sum, customfield_10028.sum)
Learn more about the available expression variables you can use in your formula and rollup expressions.
Available expression variables
To see which fields are available for use in your formula and rollup expressions, click the Available expression variables button.
A table will be displayed listing all fields that can be used.
- To reference a field in a formula expression, use the variable name listed in the Variables column (e.g.
timespent). - To reference a field in your rollup expression, use the variable name listed in the Variables column and append the appropriate aggregation method (e.g.
timespent.sum).
The fields reference also provides a non-exhaustive list of supported fields that may be used as expression variables.
Arithmetic functions
Several other arithmetic functions are available:
| Function | Description | Example expression | Result |
|---|---|---|---|
abs(x) | Calculate the absolute value of a number | abs(-3) | 3 |
ceil(x) | Round a number up to the nearest integer | ceil(4.3) | 5 |
floor(x) | Round a number down to the nearest integer | floor(4.3) | 4 |
round(x) | Round a number to the nearest integer | round(4.3) | 4 |
Complex logic
Formula expressions can be used for more complex logic by combining the basic operations described above with comparison tests, logical tests, and conditionals.
Comparison tests
Formula expressions support the following comparison tests:
- Operator notation
- Function notation
| Test | Description | Operator | Example expression | Result |
|---|---|---|---|---|
| Equal | Test whether two values are equal | == | 2 == 3 | false |
| Unequal | Test whether two values are unequal | != | 2 != 3 | true |
| Smaller than | Test whether the left value is smaller than the right value | < | 2 < 3 | true |
| Larger than | Test whether the left value is larger than the right value | > | 2 > 3 | false |
| Smaller than or equal to | Test whether the left value is smaller than or equal to the right value | <= | 2 <= 3 | true |
| Larger than or equal to | Test whether the left value is larger than or equal to the right value | >= | 2 >= 3 | false |
| Test | Description | Function | Example expression | Result |
|---|---|---|---|---|
| Equal | Test whether two values are equal | equal(x, y) | equal(2, 3) | false |
| Unequal | Test whether two values are unequal | unequal(x, y) | notEqual(2, 3) | true |
| Smaller than | Test whether value x is smaller than value y | smaller(x, y) | smaller(2, 3) | true |
| Larger than | Test whether value x is larger than value y | larger(x, y) | larger(2, 3) | false |
| Smaller than or equal to | Test whether value x is smaller than or equal to value y | smallerEq(x, y) | smallerEq(2, 3) | true |
| Larger than or equal to | Test whether value x is larger than or equal to value y | largerEq(x, y) | largerEq(2, 3) | false |
Formula fields only return numeric results. The example expressions above will be evaluated correctly but will return empty values if used in isolation. Comparison tests should be used in conjunction with other operations to perform more complex calculations that return a numeric result.
Logical tests
Formula expressions support the following logical tests:
- Operator notation
- Function notation
| Test | Description | Operator | Example expression | Result |
|---|---|---|---|---|
| Logical and | Test whether two values are both non-zero/non-empty | and | 2 and 0 | false |
| Logical not | Test whether a value is zero/empty | not | not 0 | true |
| Logical or | Test whether either of two values are non-zero/non-empty | or | 2 or 0 | true |
| Logical xor | Test whether exactly one of two values is non-zero/non-empty | xor | 2 xor 0 | true |
| Test | Description | Function | Example expression | Result |
|---|---|---|---|---|
| Logical and | Test whether values x and y are both non-zero/non-empty | and(x, y) | and(2, 0) | false |
| Logical not | Test whether value x is zero/empty | not(x) | not(0) | true |
| Logical or | Test whether value x or value y are non-zero/non-empty | or(x, y) | or(2, 0) | true |
| Logical xor | Test whether exactly one of x or y is non-zero/non-empty | xor(x, y) | xor(2, 0) | true |
Formula fields only return numeric results. The example expressions above will be evaluated correctly but will return empty values if used in isolation. Logical tests should be used in conjunction with other operations to perform more complex calculations that return a numeric result.
Conditionals
Formula expressions support conditional logic using the ternary operator. For example, the following expression uses the estimated cost of labour (from the example above), but if the estimated cost is less than $150, it returns $150 instead:
- Operator notation
- Function notation
// Formula expression to calculate the estimated cost of labour, with a minimum value of $150
// 'formula_labour_cost' is the field key for the 'Estimated cost of labour' formula field created earlier
formula_labour_cost > 150 ? formula_labour_cost : 150
// Formula expression to calculate the estimated cost of labour, with a minimum value of $150
// 'formula_labour_cost' is the field key for the 'Estimated cost of labour' formula field created earlier
larger(formula_labour_cost, 150) ? formula_labour_cost : 150
Another example is the following expression, which returns 1 if the Time Spent is greater than the Original Estimate. This formula could be used to report on issues that are over budget (e.g. by configuring a filtering rule that filters for rows where the formula field has a value of 1).
- Operator notation
- Function notation
// Formula expression to identify issues that are over budget
// 'timespent' is the field key for Jira's 'Time Spent' field
// 'timeoriginalestimate' is the field key for Jira's 'Original Estimate' field
timespent > timeoriginalestimate ? 1 : 0
// Formula expression to identify issues that are over budget
// 'timespent' is the field key for Jira's 'Time Spent' field
// 'timeoriginalestimate' is the field key for Jira's 'Original Estimate' field
larger(timespent, timeoriginalestimate) ? 1 : 0