Structure's Formula Language (Expr)

There are a few ways to add formulas to a structure:

• Adding a Formula Column - this is where you’ll do most of your formula work

• Adding a Bundled Formula - a great way to get started with formulas

• Using a Sort by Formula Generator - a powerful way to organize your issues

The guides below will cover the main elements of formulas and how to use them.

Variables are used to represent values (numbers, text strings, arrays, etc.) within a formula and can represent:

• Jira issue fields

• Calculated attributes like Progress

• Structure-specific attributes like Item type

• Attributes provided by other Jira apps

• Another formula

• Values from another Structure column

Naming Variables

Variable names can contain:

• letters (English only)

• numbers

• underscore (_) characters

Variables cannot contain spaces. The first character must be a letter or an underscore.

Examples:

• priority

• sprintName

• remaining_estimate

• abc11

As you write your formula, Structure attempts to map your variables to well-known sources. For example, the "remaining_estimate" variable above will automatically be mapped to the Remaining Estimate field. If it can’t map the variable automatically, you will need to select a value for the variable.

Variable names are not case-sensitive. Priority, priority and pRiOrItY will all refer to the same variable.

Learn more: Mapping Variables, Predefined Variables

Property Access

Formulas can also access the value of an item's property using the following notation: object.property

fixVersion.releaseDate  //returns the release date for the fixVersion

For a complete list of supported properties, see Item Properties.

Learn more: Expr Advanced Reference - Property Access

Local Variables

Local variables are similar to Variables, but they are defined locally within the formula, rather than being mapped to an attribute or Jira field. This can be helpful when an expression needs to be used more than once in the same formula. Instead of repeating the expression, you can turn it into a local variable, using  WITH :

WITH total_time = timeSpent + remainingEstimate :
  IF total_time > 0 :
     timeSpent / total_time

In this example, declaring a local variable “total_time” ensures that every instance of total_time is exactly the same, and if you ever need to change how total_time is calculated, you only have to change it once.

Learn more: Expr Advanced Reference - Local Variables

Functions are predefined expressions that can be used within a formula. Functions calculate a value based on their arguments.

• A simple function formula: SUM(9, 3). This function takes the input values (9 and 3), adds them together, and returns a sum: 12.

• A more useful formula: SUM(-original_estimate, remaining_estimate, time_spent) By summing these values, you can see how far over your original estimate you’re tracking.

Function Calls

A function call is written as the function name, followed by parentheses, which may or may not contain arguments.

Examples:

• SUM(-original_estimate, remaining_estimate, time_spent)

• CASE(priority, 'High*', 5, 1)

• TODAY()

Function names are not case-sensitive: you can write TODAY() or Today().

There are 100+ standard functions available with Structure – see Standard Functions for a complete list.

Function arguments may be separated by comma (,) or semicolon (;). But you need to be consistent: use either all commas or all semicolons.

Learn more: Expr Advanced Reference - Functions

Aggregate Functions

Aggregate functions calculate the total value from multiple rows within a structure. Unless otherwise specified, this includes the issue and all its sub-issues.

Examples:

• SUM { remaining_estimate + time_spent } – calculates the total effort (estimated and actual) for the issue and all its sub-issues.

• MAX { resolved_date - created_date } – calculates the maximum time it took to resolve an issue, among the issue and its sub-issues.

Aggregate functions are written similar to standard functions, except they use curly braces: SUM{x}.

Local variables used inside an aggregate function must also be declared inside the function - within the { } .

For a complete list of available Aggregate functions, see: Aggregate Functions

To learn more about using Aggregate functions, see: Expr Advanced Reference - Aggregate Function

Aggregate Function Modifiers

Aggregate Functions can also contain modifiers, which influence how the aggregation works. Modifiers always begin with the hash sign (#) and are placed after the function name and before the first curly brace:

• SUM#all { business_value } – this will force the function to include values from all duplicate items in the total. (By default, duplicates are ignored.)

Each aggregate function supports a specific set of modifiers, not all of them. Using an incompatible modifier will result in an error.

For a complete list of available Aggregate modifiers, see: Aggregate Functions

To learn more about using Aggregate functions and their modifiers, see: Expr Advanced Reference - Aggregate Function

Chained Function Calls

If you need to apply a sequence of functions to the same value, you can simplify this using the chained notation: listing each function one after the other, separated by a dot (.).

• Standard notation: Funciton3(Function2(Function1(x)))

• Chain notation: x.Function1().Function2().Function3()

When you use the chain notation, the value that comes before the dot becomes the first argument for the next function. Any additional arguments must be written after the function, in parentheses.

For example:

created.FORMAT_DATETIME("yyyy").CONCAT(" year issue")

In this example, FORMAT_DATETIME takes the date value in "created" and formats it based on the argument in parenthesis ("yyyy"). CONCAT takes the result from FORMAT_DATETIME and joins it with " year issue".

User Functions

A user function allows you to define a locally-used function within a formula.

Local functions are declared using the following construct:

WITH function_name(argument) = operation_to_perform :

Example:

WITH square(x) = x * x : 
  square(impactField) / square(storyPoints)

Learn more: Expr Advanced Reference - User Functions

You can include arithmetic operations, comparisons, logical operations, and text operations within a formula.

Learn more: Expr Advanced Reference - Operators.

Order of Operations

When several types of operations are used, they are done in the following order:

1. Arithmetic operations

2. Text operations (CONCAT)

3. Comparison operations

4. Logical operations. 

Conditional expressions (IF/ELSE) can be used to switch between two or more expressions, based on whether specified conditions are true (truthy) or false.

A simple "IF" expression can be declared using the IF() function, but for more elaborate IF cases, with multiple conditions and/or requiring an ELSE option, a conditional expression can be used:

WITH total = x + y:
  IF total > 0:
     x / total
  ELSE : error

The : after ELSE is optional – in the example above, we've included it for readability.

Learn more: Expr Advanced Reference - Conditional Expressions

The following number types are supported in Structure formulas:

• Whole numbers

• Decimals

• Fractions

The following elements are not supported:

• Commas

• Spaces

• Locale-specific

• Percentage

• Currency

• Scientific formats

If you need to use locale-specific decimals or thousand separators, write them as text values. Structure will convert it to a supported number:

• "1 122,25" * 2 → 2244.5

Learn more: Expr Advanced Reference - Values and Types

Text values should be enclosed in single quotes (') or double quotes (").

Examples:

• "Your text here!"

• 'Your text here!'

If the text value itself contains quotes, do one of the following:

• Insert a backslash (\) before each quote.

• Use one type of quote within the text, and another to enclose the text

Example: The following both represent represent the text value Charlie "Bird" Parker:

• "Charlie \"Bird\" Parker" 

•  'Charlie "Bird" Parker'

Learn more: Expr Advanced Reference - Values and Types

Text Snippets

Text Snippets allow you to generate strings using variables and expressions. This is particularly helpful in formulas that utilize markdown.

When using text snippets:

• The snippet should start and end with three sets of double quotes (""")

• Variables names should be preceded by $

• Expressions should start with $ and be enclosed in braces { }

""" The sum of $var1 + $var2 = ${var1 + var2}. """
 
""" This $glass is half-${IF optimist: 'full' ELSE: 'empty'}. """

Learn more: Expr Advanced Reference - Values and Types

Comments can be added to a formula to provide context or an explanation of what is being calculated, without affecting the formula itself.

• To add a single line of comment, begin the comment with //

• To add multiple lines of comment, start the comment with /* and end the comment with */

// This is a single-line comment.

/* This is a multi-line comment.
   It can be useful for longer explanations. */