Skip to main content
Skip table of contents

Aggregate Function Reference

All standard aggregate functions and available modifiers are listed on this page. 

An aggregate function call contains an expression in curly braces ("{}"), which is calculated for the item and all sub-items (or, in some cases, for another subset of related items in the structure), and then the resulting values are aggregated according to the meaning of the aggregate function.

Aggregation Functions

AVG

Avg calculates an average of the defined values for the item and/or its sub-items. The result for avg is generally the same as sum/count. It returns nothing if there are no defined values for {x}.

If a certain issue (or another kind of item) is included multiple times in the sub-tree, the average value will include the value for that issue only once. This behavior can be overridden by using the #all modifier.

Accepts modifiers: .Aggregate Function Reference v9.2##all.Aggregate Function Reference v9.2##children.Aggregate Function Reference v9.2##leaves.Aggregate Function Reference v9.2##strict.Aggregate Function Reference v9.2##subtree, .Aggregate Function Reference v9.2##preceding.Aggregate Function Reference v9.2##levels (together with #preceding), .Aggregate Function Reference v9.2##baseLevel (together with #preceding).

COUNT

Count calculates a count of defined values (or truthy values, if the #truthy modifier is specified) for the item and/or its sub-items.

If a certain issue (or another kind of item) is included multiple times in the sub-tree, it will be counted only once. This behavior can be overridden by using the #all modifier.

Accepts modifiers: .Aggregate Function Reference v9.2##all.Aggregate Function Reference v9.2##children.Aggregate Function Reference v9.2##leaves, #level.Aggregate Function Reference v9.2##strict.Aggregate Function Reference v9.2##subtree.Aggregate Function Reference v9.2##truthy, .Aggregate Function Reference v9.2##preceding.Aggregate Function Reference v9.2##levels (together with #preceding), .Aggregate Function Reference v9.2##baseLevel (together with #preceding).

JQL

Returns true if the current row is an issue and it matches this JQL. 

This is not an aggregate function; it's an embedded query. We've included it here because it's easy to mistake for an aggregate function - both use curly braces { }. 

To learn more, see Embedded Queries.

MAX

Max returns the maximum defined value for the item and/or its sub-items. Numeric, date, duration and text fields can be compared. Text fields are compared lexicographically.

If the formula produces an array, this will find the maximum element within that array.

Accepts modifiers: .Aggregate Function Reference v9.2##children.Aggregate Function Reference v9.2##leaves.Aggregate Function Reference v9.2##strict.Aggregate Function Reference v9.2##subtree.

MIN

Min returns the minimum defined value for the item and/or its sub-items. Numeric, date, duration and text fields can be compared. Text fields are compared lexicographically.

If the formula produces an array, this will find the minimum element within that array.

Accepts modifiers: .Aggregate Function Reference v9.2##children.Aggregate Function Reference v9.2##leaves.Aggregate Function Reference v9.2##strict.Aggregate Function Reference v9.2##subtree.

PARENT

Parent extracts the value from the parent row or from an ancestor row by a specified level.

Using the #level modifier, you can specify which row to extract the value from:

  • PARENT#level=-1{} extracts the value from the parent row (same as PARENT{}) 
  • PARENT#level=-2{} extracts the value from the grandparent row
  • PARENT#level=1{} extracts the value from the root row

Accepts modifier: .Aggregate Function Reference v9.2##level

PERCENTILE

Calculates a percentile value from the defined values for the item and/or its sub-items. The hierarchy of values is ignored – all subject values are treated equally.

Must include the modifier #p, followed by the desired percentile (p=0.95, p=95). p=1 interpreted as 1%.

Undefined are ignored. Non-number values result in an error.

Accepts modifiers: .Aggregate Function Reference v9.2##ancestors.Aggregate Function Reference v9.2##children.Aggregate Function Reference v9.2##leaves.Aggregate Function Reference v9.2##strict.Aggregate Function Reference v9.2##subtree.Aggregate Function Reference v9.2##fromLevel.Aggregate Function Reference v9.2##toLevel, #p (required)

SJQL

Returns true if the current row matches this S-JQL.

This is not an aggregate function; it's an embedded query. We've included it here because it's easy to mistake for an aggregate function - both use curly braces { }. 

To learn more, see Embedded Queries.

SUM

Sum calculates a numerical total for the values calculated for the item and/or its sub-items. 

Other variations of SUM allow different types of aggregation:

  • SUM{} (the same as SUM#subtree{}) aggregates values from all descendants 
  • SUM#children{} aggregates values from direct children only
  • SUM#leaves{} aggregates values from leaves
  • SUM#preceding{} aggregates values from the preceding rows
  • If the formula produces an array, calculates the total of the elements of that array

Note that when the value of the expression under aggregation is not numeric (and cannot be converted to a number), it is ignored.

If a certain issue (or another kind of item) is included multiple times in the sub-tree, the sum will include the value for that issue only once. This behavior can be overridden by using the #all modifier.

Accepts modifiers: .Aggregate Function Reference v9.2##all.Aggregate Function Reference v9.2##childrenAggregate Function Reference##leaves, #level, #level, .Aggregate Function Reference v9.2##preceding.Aggregate Function Reference v9.2##strict.Aggregate Function Reference v9.2##subtree.Aggregate Function Reference v9.2##levels (together with #preceding), .Aggregate Function Reference v9.2##baseLevel (together with #preceding).

Aggregation Modifiers

#afterChildren

Defines the exit separator between children and parent rows. This modifier has a string parameter. The default exit separator is:

  • "(" - for #beforeChildren
  • ")" - for #afterChildren
Example

CODE
JOIN#subtree#beforeChildren="<{"#afterChildren="}>"{X}

Can be used with: join.

#all

Tells the aggregate function to include duplicate items. By defaults, functions that count values ignore duplicate items.

Example

CODE
SUM#all{X}
COUNT#all{X}

Can be used with: sumcountavg.

#ancestors

Only process ancestors of the current row. This is the default behavior for join.

Can be used with: arrayjoinmedianpercentilequartile1quartile3values.

#baseLevel

Can be used with .Aggregate Function Reference v9.2#SUM with the #preceding modifier to specify at which level the accumulation will be reset and start over from zero. This allows for accruing independent cumulative values in different sub-trees.

  • By default, baseLevel is 0, which means that the accumulation will never start over and will cover the whole structure.
  • If baseLevel is not zero, then the accumulation will be restarted once it reaches a row at the base level or higher in the hierarchy. For example, if you have epics at the top level, and stories underneath them, SUM#preceding#baseLevel=1{story_points} will accumulate the Story Points within the scope of each epic independently.

Can be used with: avg, countsum (together with #preceding).

#children

Only process direct children of the current row.

Example

CODE
JOIN#children{X}
SUM#children{X}

Can be used with: arraysumcountavgjoin, min, max, medianpercentilequartile1quartile3values.

#compact

Ignores undefined values when collecting an array.

This modifier is implicitly turned on by applying the  .Aggregate Function Reference v9.2##distinct modifier to the join aggregate function.

Can be used with: array.

#depth

Same as .Aggregate Function Reference v9.2##level modifier, except #depth cannot be used with SUM, COUNT, or AVG.

#distinct

#distinct with ARRAY

Makes array only produce an array of distinct values. A duplicate value won't be added more than once if this modifier is on.

Example


CODE
ARRAY#distinct{X}


#distinct with JOIN

Makes join only concatenate distinct values. A duplicate value won't be added more than once if this modifier is on. When used with arrays, removes undefined values and performs one-step flattening.

Modifiers .Aggregate Function Reference v9.2##beforeChildren and .Aggregate Function Reference v9.2##afterChildren don't work when this option is on. 

Example

CODE
JOIN#distinct{X}
JOIN#subtree#distinct{X}

CODE
JOIN#distinct{array}

Can be used with: join.

#flatten

When collecting values from sub-items (or another subset of related items) if a value is an array, includes all the elements instead of just including the array.

This modifier is implicitly turned on by applying the  .Aggregate Function Reference v9.2##distinct modifier to the join aggregate function.

Can be used with: array.

#fromDepth
Same as .Aggregate Function Reference v9.2##fromLevel, except #fromDepth cannot be used with MEDIAN, PERCENTILE, QUARTILE1, or QUARTILE3.

#fromLevel

Specifies the position of the first row the aggregate function should take as input for a sequence.

Position is specified by an integer parameter denoted as n below:

  • Positive values mean the absolute depth of the row in the structure, e.g. n=1 means root.
  • Negative values mean the depth relative to current row, e.g. n=-1 is the current item's direct parent.
  • Default is 1.
  • Zero means the "super-root" row, which is a fictional parent of all the top rows. It can be used to get the value of another aggregate function applied to the whole structure. For example, JOIN#fromLevel=0{MIN{due_date}} will provide a sequence of the earliest due dates, starting from the earliest throughout the whole structure, then the earliest throughout the root tree this item is in, and so on.

This modifier does not work with any tree types except .Aggregate Function Reference v9.2##ancestors.

Example

CODE
JOIN#fromLevel=-1{X}
JOIN#fromLevel=2 {X}

Can be used with: arrayjoinmedianpercentilequartile1quartile3values.

#leaves

Only process leaves (items without children) in the subtree of the current row.

Example

CODE
JOIN#leaves{X}
SUM#leaves{X}

Can be used with: arraysumcountavgjoin, min, max, medianpercentilequartile1quartile3values.

#level

When used with .Aggregate Function Reference v9.2#PARENT, specifies the position of the parent that possesses value. 

Position is specified by an integer parameter denoted as n below:

  • Positive values mean the absolute depth of the row in the structure: n=1 means root element, n=2 means an element at the 2nd level, etc.
  • Negative values mean the depth relative to the current row: n=-1 is the current item's direct parent.
  • Default is -1.
  • Zero means the "super-root" row, which is a fictional parent of all the top rows. It can be used to get the value of another aggregate function applied to the whole structure. For example, PARENT#level=0{SUM{story_points}} means total story points for the whole structure (including subtrees for all roots).

When used with .Aggregate Function Reference v9.2#SUM with the #preceding modifier, this specifies the level at which the values should be aggregated.

Example

CODE
PARENT#level=-1{X}   // default one
PARENT#level=-2{X}   // "grandparent"
PARENT#level=1 {X}   // root row
PARENT#level=2 {X}

Can be used with: parentsum (together with #preceding), count (together with #preceding), avg (together with #preceding).

#levels

Can be used with .Aggregate Function Reference v9.2#SUM with the #preceding modifier to specify at which levels should the accrual of the values happen. 

  • It can be a single numeric value, for example: SUM#preceding#levels=1{story_points} will accumulate Story Points from top to bottom at level 1.
  • It can be a list of numbers, in which case the list must be wrapped in quotes: SUM#preceding#levels="2,4"{time_spent} will accumulate Time Spent on levels 2 and 4.
  • By default, all levels are counted.

Note that if you use the .Aggregate Function Reference v9.2##baseLevel modifier, only values at levels that are deeper than the base level will be counted.

You can also use .Aggregate Function Reference v9.2##level instead of #levels.

Can be used with: avgcountsum (together with #preceding).

#preceding

Can be used with .Aggregate Function Reference v9.2#SUM to calculate a numeric total of the current item and all items above it in the structure.

Can be combined with the following modifiers:

Example

preceding modifier

CODE
SUM#preceding{X}
SUM#preceding#baseLevel=1{X}
SUM#preceding#levels="1,3"{X}

Can be used with: sum, count, avg.

#reverse

Reverses the order of row processing.

Example

CODE
JOIN#reverse{X}

Can be used with: arrayjoin.

#separator

Defines the separator for string joining. This modifier has a string parameter. The default is ", ".

Example

CODE
JOIN#separator="->"{X}

Can be used with: join.

#strict

Do not process the current row item as part of the aggregation.

Cannot be used together with .Aggregate Function Reference v9.2##children, .Aggregate Function Reference v9.2##ancestors or .Aggregate Function Reference v9.2##leaves, since these already exclude the current row.

Example

CODE
JOIN#strict{X}
SUM#strict{X}

Can be used with: arraysumcountavgjoin, min, max, medianpercentilequartile1quartile3values.

#subtree

Process the whole subtree of the current row. This is the default behavior for sumcountavgminmax.

Example

CODE
JOIN#subtree{X}

Can be used with: arraysumcountavgjoinminmax, medianpercentilequartile1quartile3values.

#toDepth

Same as .Aggregate Function Reference v9.2##toLevel, except #toDepth cannot be used with MEDIAN, PERCENTILE, QUARTILE1, or QUARTILE3.

#toLevel

Specifies the position of the last row the aggregate function should take as input for a sequence.

Position is specified by an integer parameter denoted as n below:

  • Positive values mean the absolute depth of row in the structure, e.g. n=1 means root.
  • Negative values mean the depth relative to current row, e.g. n=-1 is the current item's direct parent.
  • 0 means current row.
  • Default is 0.

This modifier does not work with any tree types except .Aggregate Function Reference v9.2##ancestors.

Example

CODE
JOIN#toLevel=-1{X}
JOIN#toLevel=2 {X}

Can be used with: arrayjoinmedian, percentile, quartile1, quartile3, values.

#truthy

Only count row if the subexpression produces a truthy value .

Example

CODE
COUNT#truthy{X}

Can be used with: count.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.