Expr Advanced Reference - Aggregate Function

An aggregate function calculates values for some other rows in the structure (for example, for all sub-items), aggregates these values (for example, adds them together), and produces the resulting value to be used in the formula.

For example:

SUM#children { storyPoints } – calculates total story points for all the child issues.
PARENT { fixVersion } – provides the value of the fixVersion field from the parent's issue.
VALUES { components } – collects all distinct components that are set for any of the sub-issues of the current row.

Aggregate functions allow you to calculate complex values that depend not only on the "current" item, but also on other items in relation to it.

An aggregate function starts with a name, optionally followed by modifiers, then curly braces ("{}"), and inside them – an "inner expression", which will be calculated for some other rows. You can use whitespace between any elements of the aggregate function calls.

The inner expression may return any type of value – number, text, array, and others – except a user function. You cannot pass a user function from the inner formula into the outer formula.

Available Aggregate Functions are listed in Aggregate Functions.

Aggregate Function Modifiers

An aggregate function may have one or more modifiers that govern the aspects of the function's execution. Each modifier starts with hash sign ("#"); then comes the modifier's name; optionally followed by the equals sign ("=") and a value, which can be a string or a numeric constant. If a value is omitted, it is assumed to be 1 (a representation of true in Expr).

Examples:

SUM#all { cost }
SUM#all#leaves { IF type = "story": storyPoints }
JOIN#separator=", " { key }
JOIN #separator=", " #fromDepth=0 #toDepth=-1 { key }

Each aggregate function supports a specific set of modifiers, not all of them. Using an incompatible modifier will result in an error. To learn more about available modifiers and their restrictions, see Aggregation Modifiers.

Sharing Values Between Outer and Inner Formulas

It's important to understand that the formula inside an aggregate function – the inner formula – is calculated fully separately from the "outer" formula. Both formulas will share variable mappings (to fields), but any local variables and user functions defined on one side will not be accessible from the other side.

Will not work! Correct version

CODE

// Cannot use "total" on line 4!

WITH total = SUM#children { storyPoints } :
WITH median = MEDIAN#children { storyPoints / total } : 
...

CODE

WITH median = MEDIAN#children { 
  WITH total = PARENT { SUM#children { storyPoints } } :
  storyPoints / total 
} : 
...

As you can see from the example above, you may need to use nested aggregate functions instead.