Skip to main content
Skip table of contents

Expr Function Reference

All standard Expr functions are listed below, grouped by category. Use the right navigation panel to scroll through functions, or Ctrl+F to find a specific function.

Notes about functions

A function may take zero, one or more arguments. Some functions can take an unlimited number of arguments.

Parameter Types

Functions expect certain value types for each parameter. When an unexpected type is provided, Structure will attempt to convert it to an acceptable type. If it can't, the function may consider the value undefined and ignore the value, or it may return an error. 

For example, the MIN function is used to select the smallest number from a series; if the text value "10" is passed as of the parameters, it will convert that to the number 10. If "ABC" is passed, it won't be able to convert that to a number and will return an error.

For more information about how Structure converts values, see Value Conversions.

Undefined Values

A variable used in a formula may have undefined value. Usually it means that the value for an issue is not set – for example, Resolution field will produce undefined value until the issue is resolved. When a function that manipulates values receives an undefined value as its primary argument, the return value will also typically be undefined.

Arrays

Many functions can also be applied to arrays. When the argument is an array, it will be treated in one of the following ways:

  • Numbers and Text - If the function is expecting a number or text, it will attempt to convert the first element of the array into the appropriate type. If it fails, it will return an error. If there are more than one elements in the array, it will also return an error.
  • Text/Joined - If an array is passed as the parameter, each element in the array will be converted to text and joined together with a comma. (See Text vs. Text/Joined)
  • /Each - If an array is passed as the parameter, the function will be applied separately to each element in the array. The result will be an array with the function applied to each value.


Array Functions

ALL

ALL(A, $)

Checks that $ returns a truthy value for all elements in the array. 

ParameterTypeDescription

A

Array

Array of elements to be used in $.

$User FunctionFunction to be applied to each element.
ResultBoolean

Returns true if $ returns a truthy value for all elements in the array. Otherwise, returns false. If the array is empty, returns true.


Examples:

  • ALL(fixVersions, $.startDate > TODAY())→ Returns true if all fixVersions start after the current date.


ANY

ANY(A, $)

Checks if the array has at least one element for which $ returns true.

ParameterTypeDescription

A

Array

Array of elements to be used in $.

$User FunctionFunction to be applied to each element.
ResultBoolean

Returns true if $ returns a truthy value for at least one elements in the array. Otherwise, returns false. If the array is empty, returns false.

Examples: 

  • ANY(fixVersions, $.startDate < TODAY())→ Returns true if any fixVersions started before the current date.

ARRAY

ARRAY(Element1, Element2, ..., ElementN)

Creates an array from a list of elements.

ParameterTypeDescription

Element1, Element2, ..., ElementN

Any

Elements to be added to the array.

ResultArray

Array containing all of the elements.

Example:

  • ARRAY(1, 2, 3) → ARRAY(1, 2, 3)

COMPACT

COMPACT(A)

Removes all undefined values from the array.

ParameterTypeDescription

A

Array

Array to be compacted.

ResultArray

Compacted array.

Note: error values and empty arrays are preserved.

Examples: 

  • COMPACT(ARRAY(1, 2, undefined, 3)) → ARRAY(1, 2, 3)

CONTAINS

CONTAINS(A, Element)

Searches an array for a specified element. The comparison is done in the same way function EQ (=) works.

ParameterTypeDescription

A

Array

Array to search in.

ElementAnyElement to look for.
ResultBoolean

Returns true if the array contains the specified element. Otherwise, returns false.

Examples: 

  • CONTAINS(ARRAY(1, 2, 3), 2) → 1
  • CONTAINS(ARRAY(1, 2, 3), 5) → 0
  • fixVersions.CONTAINS("v1") → returns 1 if one of the versions in the Fix Version/s field is "v1"

Please note that CONTAINS does not perform text matching.For example, if Fix Version/s field contains version "v1.1", the above example – fixVersion.CONTAINS("v1") – will return 0. You can apply text matching to look for elements that match a particular pattern, for example: fixVersion.ANY($.MATCH("v1*"))

CONTAINS_ALL

CONTAINS_ALL(A, Elements_Array)

Searches an array for all of the elements passed in the Elements_Array parameter.

ParameterTypeDescription

A

Array

Array to search in.

Elements_ArrayArrayArray of elements to look for.
ResultBoolean

Returns true (1) if A contains all elements contained in Elements)Array. Otherwise, returns false (0).

Duplicates are not taken into account, so CONTAINS_ALL(ARRAY(1), ARRAY(1,1)) → 1 .

Examples:

  • CONTAINS_ALL(ARRAY(1, 2, 3), ARRAY(1, 2, 3)) → 1
  • CONTAINS_ALL(ARRAY(1, 2, 3), ARRAY(1, 2, 4)) → 0

CONTAINS_ANY

CONTAINS_ANY(A, Elements_Array)

Searches an array for any of the elements passed in the Elements_Array parameter.

ParameterTypeDescription

A

Array

Array to search in.

Elements_ArrayArrayArray of elements to look for.
ResultBoolean

Returns true (1) if A contains any elements contained in Elements_Array. Otherwise, returns false (0).

Examples: 

  • CONTAINS_ANY(ARRAY(1, 2, 3), ARRAY(2, 9, 7)) → 1
  • CONTAINS_ANY(ARRAY(1, 2, 3), ARRAY(4, 9, 7)) → 0

FILTER

FILTER(A, $)

Filters the values in an array and produces a new array that retains only elements for which the user function $ returns a true value (considered as a Boolean).

ParameterTypeDescription

A

Array

Array of elements to be filtered.

$User FunctionFunction to be applied to each element.
ResultArray

New filtered array.


Example: 

  • ARRAY(100, 200, 300).FILTER(x -> x < 250) → ARRAY(100, 200) 
  • worklogs.FILTER($.author = ME()) → Returns an array containing worklog values created by the current user


FIRST

FIRST(A)

Returns first element of the array, or undefined if the array is empty.

ParameterTypeDescription

A

Array

Array of elements.

ResultAny

First element contained in the array. If empty, returns undefined.

Example: 

  • FIRST(ARRAY(1, 2,3)) → 1

FLATTEN

FLATTEN(A)

Given an array of arrays, makes a single array, using one-step flattening (each element in the sub-arrays is added to the top array as a single element). 

ParameterTypeDescription

A

Array

Array of arrays, or array containing arrays and non-array values.

ResultArray

A single array, containing all elements.

Example:

  • FLATTEN(ARRAY(ARRAY(1, 2), 100, ARRAY(2, 3), 10)) → ARRAY(1, 2, 100, 2, 3, 10)

GET

GET(A, Index)

Retrieves an element from an array based on its index. Array indexes are 0-based. Returns undefined if index is out of array bounds.

ParameterTypeDescription

A

Array

Array to search.

IndexIntegerNumeric index, 0-based: 0 corresponds to the first value in A, 1 corresponds to the second, and so on.
ResultAny

The value corresponding to Index.

Example:

  • GET(ARRAY(1, 25, 2, 18, 100), 1) → 25

GROUP

GROUP(A, $)

Groups array elements into buckets based on the value produced by the user function $ for each element. The result is an array of groups (key-value maps).

ParameterTypeDescription

A

Array

Array of elements to be grouped.

$User FunctionFunction to be applied to each element.
ResultArray

Array of groups. Each group G has G.group which contains the value that the user function produced, and G.elements, which contains an array of all elements that produced that value. The order of groups corresponds to the order of the grouping values as they first appear in the source array.

Example:

Suppose the worklogs attribute contains the following work logs for the issue:

  • Author: Bob, Time Spent: 1 hour, Date: Feb-1
  • Author: Alice, Time Spent: 2 hours, Date: Feb-1
  • Author: Bob, Time Spent: 3 hours, Date: Feb-2

Let's write this using the following pseudo-formula: 

  • worklogs = ARRAY( (author: Bob, timeSpent: 1h, startDate: Feb-1)(author: Alice, timeSpent: 2h, startDate: Feb-1)(author: Bob, timeSpent: 3h, startDate: Feb-2) )

Then the following examples show grouping these work logs by author and by date:

  • worklogs.GROUP($.author) →
    ARRAY(
      (group: Bob,   elements: ARRAY( (author: Bob, timeSpent: 1h, startDate: Feb-1)(author: Bob, timeSpent: 3h, startDate: Feb-2) )),
      (group: Alice, elements: ARRAY( (author: Alice, timeSpent: 2h, startDate: Feb-1) ))
    )

  • worklogs.GROUP($.startDate) →
    ARRAY(
      (group: Feb-1, elements: ARRAY( (author: Bob, timeSpent: 1h, startDate: Feb-1)(author: Alice, timeSpent: 2h, startDate: Feb-1) )),
      (group: Feb-2, elements: ARRAY( (author: Bob, timeSpent: 3h, startDate: Feb-2) ))
    )

The expressions above show the key-value map values using a pseudo-formula. It is used only to demonstrate the values; Expr currently does not support this language or any way to create arbitrary key-value maps.

INDEX_OF

INDEX_OF(A, Element)

Finds the first occurrence of an element in the array. The comparison is done in the same way function EQ (=) works.

ParameterTypeDescription

A

Array

Array to be searched.

ElementAnyElement to search for.
ResultInteger

Returns an index of a first occurrence of a specified element in an array. If the element is not found, returns undefined.

Note: the array is zero-based, so the first element is at Index = 0.

Example:

  • INDEX_OF(ARRAY(1,3,3,3,5), 3) → 1

INDEXES

INDEXES(A)

Creates an array of indexes of A, starting with 0. For example, an array with 3 elements would return ARRAY(0, 1, 2).

ParameterTypeDescription

A

Array

Array of elements.

ResultArray

Array of indexes, starting at 0.

Example:

  • INDEXES(ARRAY("Cat","DOG","BIRD")) → ARRAY(0,1,2)

IS_ARRAY

IS_ARRAY(Value)

Returns true if the value is an array.

ParameterTypeDescription

Value

Any

Value to check.

ResultBoolean

Returns true (1) if the Value is an array; false (0) if the Value is not not an array.

Examples:

  • IS_ARRAY(ARRAY(1,2,3)) → 1

IS_EMPTY

IS_EMPTY(A)

Returns true if the array is empty.

ParameterTypeDescription

A

Array

Array of elements.

ResultBoolean

Returns true (1) if the array is empty; false (0) if the array is not empty. Note: will return true for undefined, but false for an empty text string ("").

Examples:

  • IS_EMPTY(ARRAY("Cat","DOG","BIRD")) → 0
  • IS_EMPTY(ARRAY()) → 1

JOIN

JOIN(A)

JOIN(A, Sep, Gs, Ge) 

Produces a text string representing any value. If the given value is an array, the text representation is composed by converting each element to text (per Text parameter conversion) and joining them together using ", " as a separator. Then the joined text is put into group parentheses Gs and Ge. If an array element is an array itself, the procedure repeats recursively.

A non-default separator may be passed as the Sep parameter.

ParameterTypeDescription

A

Any

Value to convert to text.

Sep (Optional)TextOptional separator to replace ", ".
Gs (Optional)TextOptional separator to replace "(".
Ge (Optional)TextOptional separator to replace ")".
ResultText

Text comprised of all values in the array.

Example:

  • JOIN(ARRAY("Cat","Dog","Bird")) → (Cat, Dog, Bird)
  • JOIN(ARRAY(ARRAY("Cat","Dog","Bird")), ARRAY("Sheep", "Pig")), " + " , "{" , "}" ) → {{Cat + Dog + Bird} + {Sheep + Pig}}
  • JOIN("Cat") → (Cat)

LAST

LAST(A)

Returns the last element of the array, or undefined if the array is empty.

ParameterTypeDescription

A

Array

Array of any type.

ResultAny

Last element contained in the array. If empty, returns undefined.

Example:

  • LAST(ARRAY(1, 2, 3)) → 3

LAST_INDEX_OF

LAST_INDEX_OF(A, Element)

Finds the last occurrence of an element in the array. 

ParameterTypeDescription

A

Array

Array to be searched.

ElementAnyElement to search for.
ResultInteger

Returns an index of a last occurrence of a specified element in an array. If the element is not found, returns undefined.

Note: the array is zero-based, so the first element is at Index = 0.

Example:

  • LAST_INDEX_OF(ARRAY(1,2,2,2,3), 2) → 3 

MAP

MAP(A, $)

Applies the user function to every element of the array.

ParameterTypeDescription

A

Array

Array of elements to be mapped.

$User FunctionFunction to be applied to each element.
ResultArray

Array containing the results.


Example: 

  • ARRAY(1, 2, 3).MAP(x -> x * 100) → ARRAY(100, 200, 300) 
  • affectedVersions.MAP($.releaseDate - $.startDate)


MERGE_ARRAYS

MERGE_ARRAYS(Array1, Array2, ..., ArrayN)

Produces a single array with the elements of all parameter arrays. Equal to ARRAY(a1, a2, ...).FLATTEN().

ParameterTypeDescription

Array1, Array2, ..., ArrayN

Array

Arrays to be grouped.

ResultArray

Single array containing all elements.

Example: 

  • MERGE_ARRAYS(ARRAY(1, 2, 3),ARRAY(4,5,6),ARRAY(7)) → ARRAY(1,2,3,4,5,6,7) 

NONE

NONE(A, $)

Checks that $ returns false for all elements in the array. 

ParameterTypeDescription

A

Array

Array of elements to be used in $.

$User FunctionFunction to be applied to each element.
ResultBoolean

Returns true (1) or false (0). If the array is empty, returns true (1).

This function is the inverse of ANY()

Examples:

  • NONE(fixVersions, $.startDate < TODAY())→ Returns true if no fixVersions started before the current date.

RECURSIVE_FLATTEN

RECURSIVE_FLATTEN(A)

Performs recursive flattening and compacting of the array. The resulting array is guaranteed to be flat and not contain undefined values.

ParameterTypeDescription

A

Array

Array of arrays.

ResultArray

A single array, containing all elements with no undefined values.

Examples:

  • RECURSIVE_FLATTEN(ARRAY(ARRAY(1, undefined, 2), ARRAY(2, 3), 100)) → ARRAY(1, 2, 2, 3, 100)

REDUCE

REDUCE(A, $)

Reduces an array to a single value based on $

ParameterTypeDescription

A

Array

Array to be reduced.

$User FunctionFunction containing two parameters.
Result

Any

  • Applies the function to the first two elements in the array. Then applies the function again, using the resulting value and the third element. And so on.
  • For an empty array, returns undefined.
  • For an array with one element, returns that element.

Examples:

  • ARRAY(2, 3, 2, 1, 2).REDUCE((a, b) -> a * b) → 24

REVERSE

REVERSE(A)

Reverses the order of elements in the array.

ParameterTypeDescription

A

Array

Array of elements.

ResultArray

Array with elements in reverse order.

Example:

  • REVERSE(ARRAY(1, 2, 3, 4)) → ARRAY(4, 3, 2, 1)

SEQUENCE

SEQUENCE(from, to)

Creates an array of integer numbers, starting with from and ending with to (inclusive). If to is less than from, the sequence will be descending.

ParameterTypeDescription

from

Integer

Starting integer.

toIntegerEnding integer.
ResultArray

Array of integers.

Examples:

  • SEQUENCE(3, 6) → ARRAY(3, 4, 5, 6)
  • SEQUENCE(6, 3) → ARRAY(6, 5, 4, 3)

SIZE

SIZE(A)

Returns the number of elements in the array.

ParameterTypeDescription

A

Array

Array of elements.

ResultInteger

Number of elements contained in the array.

Example:

  • SIZE(ARRAY(1, 2, 3, 4)) → 4
  • SIZE(ARRAY(1, ARRAY(2, 3, 4), undefined)) → 3

SORT

SORT(A)

Sorts the array using a natural order of elements: numbers, text values, item values, and then array values (which are compared with respect to all the non-array elements). Item values are compared first by item type (lexicographically, so a user would come before a version because "u" comes before "v") and then either by the item's natural order, if it exists, or by the item's text representation, if it doesn't.

ParameterTypeDescription

A

Array

Array of elements to be sorted.

ResultArray

Sorted array.

Example:

  • SORT(ARRAY(3,1,2)) → ARRAY(1,2,3)


SORT_BY

SORT_BY(A, $)

Sorts the array by comparing the values produced by calling the user function $ for each array element. Results of the user function $ calls are compared the same way as the SORT function.

ParameterTypeDescription

A

Array

Array of elements to be sorted.

$User FunctionFunction to be applied to each element.
ResultArray

Sorted array.

Example:

  • SORT_BY(fixVersions, $.releaseDate)


SUBARRAY

SUBARRAY(A, from, to)

Produces an array with elements from the given array. Parameters from (inclusive) and to (exclusive) define the range. Indexes are zero-based.

ParameterTypeDescription
AArrayArray of elements.

from

Integer

Starting index (inclusive).

toIntegerEnding index (exclusive).
ResultArray

Array containing elements between from and to.

Examples:

  • SUBARRAY(ARRAY("Cat", "Dog", "Mouse", "Bird", "Sheep"), 1, 3) → ARRAY("Dog", "Mouse")

UNIQUE

UNIQUE(A)

Removes duplicates from the array. The order of non-duplicate elements is preserved.

ParameterTypeDescription

A

Array

Array of elements.

ResultArray

Array with duplicates removed.

Note: the equality is strict, so "0" and 0 would be different elements.

Example:

  • UNIQUE(ARRAY(1, 2, 1, 3, 3, 4)) → ARRAY(1, 2, 3, 4)

WITHOUT

WITHOUT(A, Value)

Returns a new array with all the elements from the input array except those equal to Value

ParameterTypeDescription

A

Array

Array of elements.

ValueAnyElement to be removed.
ResultArray

New array with Value removed.

Equality is done using the same logic as the = operator and CONTAINS.

Example:

  • WITHOUT(ARRAY(1, 2, 1, 3, 3, 4), 1) → ARRAY(2, 3, 3, 4)


Conditional Functions

CASE

CASE(Value, Match1, Result1, Match2, Result2, ..., Default)

Checks if the Value matches against several checks and returns a corresponding result. 

ParameterTypeDescription
ValueText/JoinedValue to check.
Match1, Match2, ..., MatchNText/JoinedText patterns to check against. The first matching pattern will define the result. A pattern can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
Result1Result2, ..., ResultN AnyValues to return from the function, each value corresponds to the preceding Match parameter.
Default (Optional)AnyOptional default value, to be returned if none of the patterns match. If not specified, undefined is returned.
ResultAnyResult1, Result2, etc. depending on which pattern matched, or Default, or undefined.

This function is typically used to map text values to numbers.

Examples:

  • CASE(Priority; "Highest"; 10; "High"; 5; "Medium"; 3; 1)
  • CASE(Version; "V1*"; 1; "V2*"; 2)
If the Value is undefined, the function immediately returns the Default result (or undefined if there's no default), so there is usually no need to use undefined as one of the matches.

CHOOSE

CHOOSE(Index; Value1; Value2; ...)

Based on the value of Index, returns the corresponding value from the argument list.

ParameterTypeDescription
IndexNumberNumeric index, with 1 corresponding to Value1, 2 corresponding to Value2 and so on.
Value1Value2, ..., MatchNAnyThe values to pick from.
ResultAnyThe Value corresponding to Index.

Examples:

  • CHOOSE(1; "A"; "B"; "C") → "A"
  • CHOOSE(2; "A"; "B"; "C") → "B"

DEFINED

DEFINED(Value)

Checks if the value is defined. 

ParameterTypeDescription
ValueAnyValue to check.
ResultBooleanReturns false (0) if Value is undefined and true (1) otherwise.

Example:

  • IF(DEFINED(Resolution); ...)

DEFAULT

DEFAULT(Value; DefaultValue)

Substitutes DefaultValue if the Value is undefined.

ParameterTypeDescription
ValueAnyValue to check.
DefaultValueAny

Value to be returned if Value is undefined.

ResultAnyIf Value is defined, returns Value. Otherwise, returns DefaultValue.

Examples:

  • DEFAULT(100; 500) → 100
  • DEFAULT(undefined; 500) → 500

IF

IF(Condition1; Result1; Condition2; Result2; ...; Default)

Checks one or several conditions and returns the result associated with the first true condition.

ParameterTypeDescription
Condition1Condition2, ..., Condition3AnyValue to check. The values are evaluated using "truthfulness check" – the first value that is "truthy" (not undefined, not zero and not an empty string), will define the returned value.
Result1Result2, ..., ResultN AnyResults to be returned, each result corresponding to the preceding check.
Default (Optional)AnyOptional default value, to be returned if none of the patterns match. If not specified, undefined is returned.
ResultAnyResult1, Result2, etc. depending on which pattern matched, or Default, or undefined.

Examples:

  • IF(Estimate > 0; Duration / Estimate; 0)
  • IF(N = 0; "No apples"; N = 1; "One apple"; CONCAT(N; " apples"))

IFERR

IFERR(Value; FallbackValue)

Checks if calculating Value produced an error and substitutes FallbackValue instead of the error value.

ParameterTypeDescription
ValueAnyValue to check.
FallbackValueAny

Value to be returned if calculating Value produces an error.

ResultAnyIf Value calculated without an error, returns Value. Otherwise, returns DefaultValue.

Normally, if an error occurs while calculating a formula, it is propagated upwards, and the result of the whole expression will be an error. This function helps circumvent that.

Example:

  • IFERR(100 / 0; 100) → 100

ISERR

ISERR(Value; ErrorCode)

Checks if calculating value produced an error. 

ParameterTypeDescription
ValueAnyValue to check.
ErrorCode (Optional)Integer

Optional error code. See Expr Error Codes for a list.

ResultBooleanReturns true (1) if there was an error. If ErrorCode is specified, returns true only if the error was of the specified error code.

Examples:

  • ISERR("Ham") → 0
  • ISERR(1 / 0) → 1
  • ISERR(1 / 0, 4) → 1  //Note: Error code 4 is an Arithmetic Error


Date and Time Functions

Date/time functions operate with a numeric representation of time. A moment in time is represented as a number of milliseconds since midnight, January 1st 1970, GMT. Negative values are allowed, representing times prior to January 1st 1970.

To display a result of a date/time calculation in a readable way, you need to either configure the Formula to use a date/time format, or use one of the conversion functions to turn the value into a human-readable text.

Many of the date / time functions depend on the current user's time zone.

DATE

DATE(Text; Locale; TimeZone)

Converts a text representation of a date to a number.

ParameterTypeDescription
TextText/EachThe text value to convert.
Locale (Optional)TextOptional locale identifier, such as "fr_FR". If not specified, user's locale is used.
TimeZone (Optional)TextOptional time zone identifier, such as "America/New_York". 
ResultNumberTimestamp, corresponding to midnight of the specified date at the specified time zone. If conversion is unsuccessful, returns an error.

The conversion uses the standard formats for representing dates:


  • Format "yyyy-MM-dd", like "2017-04-15".
  • Standard formats for the specified locale.

  • Jira formats, as specified in the Jira's system settings.

Examples:

  • DATE("2016-01-01")

  • DATE("31/Dec/16")

  • DATE("12/31/2016", "en_US", "America/New_York")

DATE_ADD

DATE_ADD(DateTime, Number, Unit)

Adds the specified integer number of seconds, minutes, hours, days, months or years to the date or date/time value. 

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
NumberIntegerThe integer number of units of time to add.
UnitTextA text value specifying the unit of time: "seconds""minutes""hours""days""months""years"
ResultDateAdjusted DATETIME.

Examples:

  • DATE_ADD(DATE("2016-01-31"), 1, "day") → DATE("2016-02-01")
  • DATE_ADD(DATE("2016-01-31"), 1, "month") → DATE("2016-02-29")
  • DATE_ADD(DATE("2016-02-29"), 1, "year") → DATE("2017-02-28")
  • DATE_ADD(DATETIME("2016-01-31 10:30:00"), 3, "hours") → DATETIME("2016-01-31 13:30:00")
  • DATE_ADD(DATETIME("2016-01-31 23:59:59"), 2, "minutes") → DATETIME("2016-02-01 00:01:59")
The second parameter (Number) must be an integer.

DATE_SET

DATE_SET(DateTime, Number, Unit)

Sets the specified part of the date or date/time to the specific integer value. Note that unlike DATE_ADD and DATE_SUBTRACT, you can specify additional units like "day_of_week".

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
NumberIntegerThe integer number to be set as the unit value in this date/time.
UnitText A text value specifying the unit of time: "second""minute""hour""day""month""year", "day_of_week".
ResultDateAdjusted DATETIME.

Examples:

  • DATE_SET(DATE("2016-01-31"), 2017, "year") → DATE("2017-01-31")
  • DATE_SET(DATE("2016-01-31"), 2, "month") → DATE("2016-02-29")
  • DATE_SET(DATETIME("2016-02-29 15:30"), 10, "day") → DATETIME("2016-02-10 15:30")
  • DATE_SET(DATE("2017-04-01"), 7, "day_of_week") → DATE("2017-04-02")
  • DATE_SET(DATETIME("2016-01-31 10:30:00"), 0, "hour") → DATETIME("2016-01-31 00:30:00")

DATE_SUBTRACT

DATE_SUBTRACT(DateTime, Number, Unit)

Subtracts the specified integer number of seconds, minutes, hours, days, months or years from the date or date/time value. 

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
NumberIntegerThe integer number of units of time to subtract.
UnitTextA text value specifying the unit of time: "seconds""minutes""hours""days""months""years"
ResultDateAdjusted DATETIME.

Examples:

  • DATE_SUBTRACT(DATE("2016-02-01"), 1, "day") → DATE("2016-01-31")
  • DATE_SUBTRACT(DATE("2016-02-29"), 1, "month") → DATE("2016-01-29")
  • DATE_SUBTRACT(DATE("2017-02-28"), 1, "year") → DATE("2016-02-28")
  • DATE_SUBTRACT(DATETIME("2016-01-31 10:30:00"), 3, "hours") → DATETIME("2016-01-31 07:30:00")
  • DATE_SUBTRACT(DATETIME("2016-02-01 00:01:59"), 2, "minutes") → DATETIME("2016-01-31 23:59:59")

DAY

DAY(DateTime)

Returns the day of the month for the given date or date/time value.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultNumberNumeric value for the day of the month. The result is calculated using the current user's time zone.

Example:

  • DAY(DATE("2017-04-15")) → 15

DAYS_BETWEEN

DAYS_BETWEEN(DateTime1, DateTime2)

Calculates the number of full days (24 hour periods) between two date or date/time values.

ParameterTypeDescription
DateTime1DateFirst date or date/time value to compare.
DateTime2DateSecond date or date/time value to compare.
ResultNumberThe number of full days between the dates. Returns a negative value if DateTime2 occurs earlier than DateTime1.

Examples:

  • DAYS_BETWEEN(DATE("2017-01-01"), DATE("2017-02-01")) → 31
  • DAYS_BETWEEN(DATE("2017-01-01"), DATE("2017-01-01")) → 0
  • DAYS_BETWEEN(DATE("2017-01-01"), DATE("2016-01-01")) → -366
  • DAYS_BETWEEN(DATETIME("2017-01-01 00:00"), DATETIME("2017-01-01 23:59")) → 0
  • DAYS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 23:58")) → 0
  • DAYS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 23:59")) → 1

DATETIME

DATETIME(Text; Locale; TimeZone)

Converts a text representation of a date and time to a numeric representation of that date and time. The resulting timestamp will correspond to the specified date and time at the specified time zone. If seconds are omitted, they will be set to zero.


ParameterTypeDescription
TextText/EachThe text value to convert.
Locale (Optional)TextOptional locale identifier, such as "fr_FR". If not specified, user's locale is used.
TimeZone (Optional)TextOptional time zone identifier, such as "America/New_York". 
ResultNumberTimestamp, corresponding to the specified date and time at the specified time zone. If seconds are omitted, they will be set to zero. If conversion is unsuccessful, returns an error.


The conversion uses the standard formats for representing dates:

  • Format "yyyy-MM-dd HH:mm:ss" and the same without seconds, like "2017-04-15 15:00" or "2017-12-31 23:59:59" (using 24-hour clock).
  • Standard formats for the specified locale.

  • Jira formats, as specified in the Jira's system settings.

Examples:

  • DATETIME("2016-01-01 00:01")

  • DATETIME("31/Dec/16 3:15 pm")

  • DATETIME("12/31/2016, 3:15 PM", "en_US", "America/New_York")

END_OF_MONTH

END_OF_MONTH(DateTime)

Sets the day in the date/time value to the end of the month. Does not change the time value.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultDateDate is set to end of the month. Time value is unchanged.

Example:

  • END_OF_MONTH(DATE("2017-04-15")) → DATE("2017-04-30")

FORMAT_DATETIME

FORMAT_DATETIME(DateTime, Format, Locale, TimeZone)

Advanced function to convert a date/time value into a text. Accepts an arbitrary format text and, optionally, locale and time zone settings. Does not depend on the current user's locale nor time zone.

ParameterTypeDescription
DateTimeDate/EachThe value to convert.
FormatTextThe format string. For all the options, please see Java documentation for SimpleDateFormat.
Locale (Optional)TextOptional locale identifier. If omitted or undefined, will use Jira's system locale. (Not the user's locale!)
TimeZone (Optional)TextOptional time zone identifier. If omitted or undefined, will use Jira's system time zone. (Not the user's time zone!)
ResultTextDateTime converted to text.

Examples:


  • FORMAT_DATETIME(DATE("2017-04-15"), "EEE, MMM d, `yy", "fr_FR") → "sam., avr. 15, `17"
  • FORMAT_DATETIME(DATETIME("2016-12-31 23:59"), "yyyy-MM-dd'T'HH:mm:ss") → "2016-12-31T23:59:00"

HOUR

HOUR(DateTime)

Returns the hour in the specified date/time value (from 0 to 23).

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric value for the hour (0 to 23).

Example:

  • HOUR(DATETIME("2017-01-01 20:15")) → 20

HOURS_BETWEEN

HOURS_BETWEEN(DateTime1, DateTime2)

Calculates the number of full hours between two date/time values.

ParameterTypeDescription
DateTime1DateFirst date/time value to compare.
DateTime2DateSecond date/time value to compare.
ResultNumberThe number of full hours between the date/time values. Returns a negative value if DateTime2 occurs earlier than DateTime1.

Examples:

  • HOURS_BETWEEN(DATE("2017-01-01"), DATE("2017-01-02")) → 24
  • HOURS_BETWEEN(DATETIME("2017-01-01 15:00"), DATETIME("2017-01-01 16:30")) → 1
  • HOURS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 00:58")) → 0
  • HOURS_BETWEEN(DATETIME("2017-01-01 23:59"), DATETIME("2017-01-02 00:59")) → 1

MAKE_DATE

MAKE_DATE(Year, Month, Day)

Creates a date value based on the numbers defining year, month and day. The time is set to midnight in the user's time zone.

ParameterTypeDescription
YearNumberYear.
MonthNumberNumber corresponding to the month.
DayNumberNumber corresponding to the day of the month.
ResultDateDate/time set to midnight in the current user's time zone.

Example:

  • MAKE_DATE(2017, 12, 31)

MAKE_DATETIME

MAKE_DATETIME(Year, Month, Day, Hour, Minute, Second)

Creates a date/time value based on the numbers defining year, month, day, hour, minute and second.

ParameterTypeDescription
YearNumberYear.
MonthNumberNumber corresponding to the month.
DayNumberNumber corresponding to the day of the month.
HourNumberHour (0-23).
MinuteNumberMinutes.
SecondNumberSeconds.
ResultDateDate/time based on the provided values. Set to the current user's time zone.

Example:

  • MAKE_DATETIME(2017, 12, 31, 23, 59, 59)

MINUTE

MINUTE(DateTime)

Returns the minutes in the specified date/time value.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberMinutes (from 0 to 59)

Example:

  • MINUTE(DATETIME("2017-01-01 20:15")) → 15

MONTH

MONTH(DateTime)

Returns the month in the specified date/time value.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric value for the month (1 to 12).

Example:

  • MONTH(DATE("2017-04-15")) → 4

MONTHS_BETWEEN

MONTHS_BETWEEN(DateTime1, DateTime2)

Calculates the number of months between two date or date/time values.

ParameterTypeDescription
DateTime1DateFirst date or date/time value to compare.
DateTime2DateSecond date or date/time value to compare.
ResultNumberThe number of full months between the date/time values. Returns a negative value if DateTime2 occurs earlier than DateTime1.

Examples:

  • MONTHS_BETWEEN(DATE("2017-01-01"), DATE("2018-01-01")) → 12
  • MONTHS_BETWEEN(DATE("2017-01-31"), DATE("2017-02-28")) → 0
  • MONTHS_BETWEEN(DATE("2017-02-28"), DATE("2017-04-28")) → 2
  • MONTHS_BETWEEN(DATE("2017-01-01"), DATE("2016-12-01")) → -1

NOW

NOW()

Returns the current date and time.

ParameterTypeDescription
ResultDateCurrent date and time.

Example:

  • NOW()

PARSE_DATETIME

PARSE_DATETIME(Text, Format, Locale

, TimeZone)


Advanced function to convert a text into a date or date/time value. Accepts an arbitrary format string and, optionally, locale and time zone settings. Does not depend on the current user's locale nor time zone.

ParameterTypeDescription
TextText/EachThe value to convert.
FormatTextThe format string. For all the options, please see Java documentation for SimpleDateFormat.
Locale (Optional)TextOptional locale identifier. If omitted or undefined, will use Jira's system locale. (Not the user's locale!)
TimeZone (Optional)TextOptional time zone identifier. If omitted or undefined, will use Jira's system time zone. (Not the user's time zone!)
ResultDateDate or date/time value for the original Text.


Examples:

  • PARSE_DATETIME("sam., avr. 15, `17", "EEE, MMM d, `yy", "fr_FR") → DATE("2017-04-15")
  • PARSE_DATETIME("2016-12-31T23:59:00", "yyyy-MM-dd'T'HH:mm:ss") → DATETIME("2016-12-31 23:59")

SECOND

SECOND(DateTime)

Returns the seconds in the specified date/time value.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberSeconds (0 to 59).

Example:

  • SECOND(DATETIME("2017-04-15 15:30:59")) → 59

START_OF_MONTH

START_OF_MONTH(DateTime)

Sets the day in the date/time value to the first day of the month. 

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultDateDate is set to first day of the month. Time value is unchanged.

Example:

  • START_OF_MONTH(DATE("2017-04-15")) → DATE("2017-04-01")

TODAY

TODAY()

Returns the current date with time set to midnight according to the current user's time zone.

ParameterTypeDescription
ResultDateCurrent date

Example:

  • TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes the time value from the date/time.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate, set to midnight in the current user's time zone.

Example:

  • TRUNCATE_TIME(DATETIME("2017-01-01 15:15")) → DATE("2017-01-01")

TRUNCATE_TO_HOURS

TRUNCATE_TO_HOURS(DateTime)

Removes the minutes, seconds and milliseconds from the date/time.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate/time, set to the last even hour in the current user's time zone.

Example:

  • TRUNCATE_TO_HOURS(DATETIME("2017-01-01 15:15")) → DATE("2017-01-01 15:00")

TRUNCATE_TO_MINUTES

TRUNCATE_TO_MINUTES(DateTime)

Removes the seconds and milliseconds from the date/time.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate/time, set to the last even minute.

Example:

  • TRUNCATE_TO_MINUTES(DATETIME("2017-01-01 15:15:15")) → DATE("2017-01-01 15:15:00")

TRUNCATE_TO_SECONDS

TRUNCATE_TO_SECONDS(DateTime)

Removes the milliseconds from the date/time.

ParameterTypeDescription
DateTimeDate/EachDate/time value.
ResultDateDate/time, with milliseconds removed.

Example:

  • TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day of the week.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric value for the day of the week. Follows ISO-8601 standard (1 – Monday, 7 – Sunday).

Example:

  • WEEKDAY(DATE("2017-04-23")) → 7

WEEKNUM

WEEKNUM(DateTime)

Returns the number of the week of the year.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultNumberNumeric value for the week of the year. Follows ISO-8601 standard.

Example:

  • WEEKNUM(DATE("2017-01-02")) → 2

YEAR

YEAR(DateTime)

Returns the year in a date or date/time value as a number.

ParameterTypeDescription
DateTimeDate/EachDate or date/time value.
ResultIntegerYear.

Example:

  • YEAR(DATE("2017-04-23")) → 2017

YEARS_BETWEEN

YEARS_BETWEEN(DateTime1, DateTime2)

Calculates the number of years between two date or date/time values.

ParameterTypeDescription
DateTime1DateFirst date or date/time value to compare.
DateTime2DateSecond date or date/time value to compare.
ResultNumberThe number of full years between the date/time values. Returns a negative value if DateTime2 occurs earlier than DateTime1.

Examples:

  • YEARS_BETWEEN(DATE("2017-01-01"), DATE("2018-01-01")) → 1
  • YEARS_BETWEEN(DATE("1703-05-27"), DATE("2017-04-23")) → 313
  • YEARS_BETWEEN(DATE("2017-06-01"), DATE("2018-05-31")) → 0


Duration Functions

Duration is represented as a number of milliseconds. To create a value or make sense of a value, you need one of the following functions to convert a string to a duration and vice versa.

You can add duration to a date or date/time value and treat the result as a new date/time, but only if it's a calendar duration. This does not work with work duration.

To understand why, let's consider you wanted to add 16 hours at a date or date/time. The result should be slightly less than a day later. However, when using work duration, adding 16 hours will result in a date at least 2 days later (maybe more, if it crosses a weekend), based on Jira's default 8h/day 5-day work week.

CALENDAR_DAYS

CALENDAR_DAYS(Duration)

Returns a number of calendar days represented by the duration value as a decimal number. 

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberNumber of calendar days. May return a fractional number of days.

Examples:

  • CALENDAR_DAYS(DURATION("10d")) → 10
  • CALENDAR_DAYS(DURATION("12h")) → 0.5

CALENDAR_HOURS

CALENDAR_HOURS(Duration)

Returns a number of hours represented by the duration value as a decimal number.

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberNumber of hours. May return a fractional number of hours.

Examples:

  • CALENDAR_HOURS(DURATION("10d")) → 240
  • CALENDAR_HOURS(DURATION("12h 45m")) → 12.75

CALENDAR_MINUTES

CALENDAR_MINUTES(Duration)

Returns a number of minutes represented by the duration value as a decimal number.

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberNumber of minutes. May return a fractional number of minutes.

Example:

  • CALENDAR_MINUTES(DURATION("3h")) → 180

CALENDAR_SECONDS

CALENDAR_SECONDS(Duration)

Returns a number of seconds represented by the duration value as a decimal number.

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberNumber of calendar seconds. May return a fractional number of seconds.

Example:

  • CALENDAR_SECONDS(DURATION("1h")) → 3600

CALENDAR_DURATION

CALENDAR_DURATION(Start, Finish, Calendar)

Returns the time spent between start and finish according to the chosen calendar. Be aware that calendars supplied by Structure do not depend on the user's time zone (system time zone is used), but some functions (like DATETIME) do. Other calendars may or may not depend on the user's time zone depending on the implementation. 

ParameterTypeDescription
StartDateDate or date/time value to start counting calendar time.
FinishDateDate or date/time value to finish counting calendar time.

Calendar
(Optional)

TextCalendar name in current user locale. If omitted, the default (24/7) calendar is used, and the result would be equal to (Finish - Start).
ResultNumberDuration in milliseconds.

Example:

  • CALENDAR_DURATION(DATE("18/Jul/2022"), DATE("19/Jul/2022"), "Standard work calendar 8/5") → 28800000 (8h in milliseconds)

DURATION

DURATION(Text)

Converts a text representation of a calendar duration to a number.

This function ignores Jira's settings for work time, so DURATION("1w") = DURATION("7d") and DURATION("1d") = DURATION("24h").To consider work time, use JIRA_DURATION.

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberDuration in milliseconds.

Examples:

  • DURATION("1w 2d 3h 4m")
  • DURATION("3d")

FORMAT_DURATION

FORMAT_DURATION(Duration)

Converts duration value to the Jira format with numbers followed by symbols specifying the time unit.

ParameterTypeDescription
DurationNumber/EachDuration value in number format.
ResultTextDuration value converted to Jira Duration format (1d 3h 30m).

Example:

  • FORMAT_DURATION(DURATION("1w 1d")) → "1w 1d"

JIRA_DAYS

JIRA_DAYS(Duration)

Returns a number of work days in the specified duration according to Jira's settings. 

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberNumber of work days. (By default, one day is 8 hours.) May return a fractional number.

Example:

  • JIRA_DAYS(DURATION("24h")) → 3
  • JIRA_DAYS(DURATION("12h")) → 1.5

JIRA_DURATION

JIRA_DURATION(Text)

Converts a text representation of a Jira work duration to a number. 

The specified time is work time, according to Jira's settings. With the default Jira settings, JIRA_DURATION("1w") = JIRA_DURATION("5d") and JIRA_DURATION("1d") = JIRA_DURATION("8h").  To use calendar time, use DURATION.

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberDuration in milliseconds.

Examples:

  • JIRA_DURATION("1w 2d 3h 4m")
  • JIRA_DURATION("3d")

JIRA_WEEKS

JIRA_WEEKS(Duration)

Returns a number of work weeks in the specified duration according to Jira's settings.

ParameterTypeDescription
DurationText/EachValue expressed in Jira Duration format (1d 3h 30m).
ResultNumberNumber of work days. (By default, one week is 5 work days.) May return a fractional number.

Example:

  • JIRA_WEEKS(JIRA_DURATION("10d")) → 2
  • JIRA_WEEKS(DURATION("5d")) → 3


Numeric Functions

ABS

ABS(Value)

Calculates the absolute value of a number.

Parameter

Type

Description

Value

Number/Each

Value to check.

Result

Number

Absolute value of Value.

Examples:

  • ABS(5) → 5

  • ABS(-4) → 4

CEILING

CEILING(Value; N)

Rounds value up to the Nth decimal place.

Parameter

Type

Description

Value

Number/Each

Number to round.

N (Optional)

Integer

How many decimal places to round up to. Negative numbers round up to tens, hundreds, etc. Default value: 0 (round to an integer).

Result

Number

Value rounded up to the Nth place.

Examples:

  • CEILING(1.678) → 2

  • CEILING(12.34; 1) → 12.4

  • CEILING(12.34; -1) → 20

  • CEILING(-3.14) → -3

FLOOR

FLOOR(Value; N)

Rounds value down to the Nth decimal place.

Parameter

Type

Description

Value

Number/Each

Number to round.

N (Optional)

Integer

How many decimal places to round down to. Negative numbers round down to tens, hundreds, etc. Default value: 0 (round to an integer).

Result

Number

Value rounded down to the Nth place.

Examples:

  • FLOOR(1.678) → 1

  • FLOOR(12.34; 1) → 12.3

  • FLOOR(17.34; -1) → 10

  • FLOOR(-3.14) → -4

LN

LN(x)

Returns the logarithm for 'x' with base of e.

Parameter

Type

Description

x

Number

Value to check

Result

Number

logarithm for x with a base of e

LOG

LOG(x), LOG(x,b)

LOG(x) returns the logarithm for 'x' with base of 10. LOG(x,b) returns the logarithm for 'x' with base of 'b'.

Parameter

Type

Description

x

Number

Value to check

b

(Optional)

Number

Base value.

If omitted or an empty value is entered, uses base 10.

Result

Number

logarithm for x

Example - the following will categorize projects based on the logarithm of all the story points within them:

WITH projectSize = LOG(SUM{storypoints}):
IF(
projectSize < 1; "Small";
projectSize < 2; "Medium";
projectSize >= 2; "Large"
)

LOG10

LOG10(x)

Returns the logarithm for 'x' with base of 10. Same as LOG with only an x variable.

Parameter

Type

Description

x

Number

Value to check

Result

Number

logarithm for x

MOD

MOD(A; N)

Returns the remainder from dividing A by N.

Parameter

Type

Description

A

Integer/Each

The dividend, must be an integer.

N

Integer

The divisor, must be an integer.

Result

Number

The remainder from dividing A by N.

Example:

  • MOD(17; 5) → 2

MUL

MUL(Value1, Value2,...)

MUL(A)

Short for "multiply" - produces the product of all values passed as arguments. When used with an array, produces the product of all values in the array. 

Parameter

Type

Description

Value1Value2, ..., ValueN

OR

A

Number

Array

Series of number values.

Array containing numeric elements.

Result

Integer

Product of all numeric elements.

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

Example:

  • MUL(2, 3, 5) → 30

  • MUL(ARRAY(1, 2, 3, 4)) → 24

NUMBER

NUMBER(Value, DefaultOpt)

Converts value to number. This function is rarely needed, because conversion to number happens automatically when needed.

Parameter

Type

Description

Value

Any

Value to convert

Default (Optional)

Number

Optional. If provided and Value cannot be converted to a number, this function returns the Default rather than an error.

Result

Number

Value converted to a number.

Example:

  • NUMBER("1.234") → 1.234

POW

POW(B; E)

Produces B to the power of E (BE). Both values can be fractional.

Parameter

Type

Description

B

Number/Each

Base

E

Number

Exponent

Result

Number

B to the power of E (BE)

Example:

  • POW(3; 3) → 27

  • POW(27; 1/3) → 3

ROUND

ROUND(Value, N)

Rounds value to the Nth decimal place.

Parameter

Type

Description

Value

Number/Each

A number to round.

N (Optional)

Integer

How many decimal places to round to. Negative numbers round to the nearest tens, hundreds, etc. Default value: 0 (round to an integer).

→ Result

Number

Value rounded to the Nth place.

Examples:

  • ROUND(1.678) → 2

  • ROUND(12.34, 1) → 12.3

  • ROUND(12.34, -1) → 10

  • ROUND(ARRAY(1.1, 2.6)) → ARRAY(1, 3)

SIGN

SIGN(Value)

Returns the sign of the Value (1 for positive, -1 for negative).

Parameter

Type

Description

Value

Number/Each

Value to check.

→ Result

Number

Returns 1 if Value is positive, -1 if Value is negative.

Examples:

  • SIGN(123) → 1

  • SIGN(0) → 0

  • SIGN(-123) → -1

SQR

SQR(Value)

Returns the passed numerical value, squared.

Parameter

Type

Description

Value

Number/Each

Numerical value.

→ Result

Number

Value2

Example:

  •  SQR(5) → 25

SQRT

SQRT(Value)

Returns the square root of the passed numerical value.

Parameter

Type

Description

Value

Number/Each

Numerical value.

→ Result

Number

Value

Example:

  • SQRT(25) → 5

SUM

SUM(Number1, Number2, ...)

SUM(A)

Produces the total of all numeric values passed as arguments. When used with an array, produces a total of all numeric elements in the array.

Parameter

Type

Description

Number1, Number2, ..., NumberN)

OR

A

Number

Array

Array containing numeric elements.

Result

Integer

Sum of all numeric elements.

Undefined values are ignored. Non-numeric values (that cannot be converted to numbers) result in an error.

Example:

  • SUM(1; 3; 5) → 9

  • SUM(ARRAY(1, 2, 3, 4)) → 10

Statistical Functions

AVERAGE

AVERAGE(Number1, Number2, ...) 

AVERAGE(A)

Calculates the average of numbers in an array, or a series of numbers. When used with multiple arguments, finds the average of the numbers passed as parameters. When used with an array, finds the average of the numbers in the array. 

ParameterTypeDescription

Number1, Number2, ..., NumberN

OR

A

Number


Array

Series of number values.


Array of numbers values to be considered.

ResultNumber

Average of the numbers. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. Values that cannot be converted to numbers produce an error.

Examples:

  • AVERAGE(1; 3; 5) → 3
  • AVERAGE(numberArray)

MAX

MAX(Number1, Number2, ...) 

MAX(A)

When used with multiple arguments, finds the largest value among the numbers passed as parameters. When used with an array, finds the maximum number in the array.

ParameterTypeDescription

Number1, Number2, ..., NumberN

OR

A

Number


Array

Series of number values.


Array of numbers values to be considered.

ResultNumber

Largest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. Values that cannot be converted to numbers produce an error.

Examples:

  • MAX(due_date; updated_date)
  • MAX(0; -10; undefined; 10) → 10
  • MAX(ARRAY(1,6,3)) → 6

MEDIAN

MEDIAN(A)

Calculates the median of the numbers in an array. Equal to PERCENTILE(A, 0.5).

ParameterTypeDescription

A

Array

Array of numbers values to be considered.

ResultNumber

Median value.

Example: 

  • MEDIAN(ARRAY(1,2,5,7,8)) → 5

MIN

MIN(Number1, Number2, ...) 

MIN(A)

When used with multiple arguments, find the smallest value among the numbers passed as parameters. When used with an array, finds the minimum number in the array.

ParameterTypeDescription

Number1, Number2, ..., NumberN

OR

A

Number


Array

Series of number values.


Array of numbers values to be considered.

ResultNumber

Smallest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. Values that cannot be converted to numbers produce an error.

Examples:

  • MIN(0; -10; undefined; 10) → -10
  • MAX(ARRAY(1,6,3)) → 1

PERCENTILE

PERCENTILE(A, N)

Calculates N percentile of the values in the given array.

ParameterTypeDescription

A

Array

Array of values to be considered.

NNumber

Value between 0.0 and 1.0. Any other value will produce an error.

ResultNumber

Resulting value.

Undefined elements are ignored. Non-numeric values that cannot be converted to a number will result in an error.

If A contains only numbers, PERCENTILE(A, 0) is equal to A.MIN() and PERCENTILE(A, 1) is equal to A.MAX().

Example:

  • PERCENTILE(ARRAY(1,2,3,4,5), 0.25) → 2

QUARTILE

QUARTILE(A, N)

Calculates the quartile of the numbers in an array, or a series of numbers. Equal to PERCENTILE(A, N*0.25).

ParameterTypeDescription

A

Array

Array of values to be considered.

NINT

Interger value between 0 and 4.

0 = value at the 0 percentile

1 = value at the 25th percentile

2 = value at the 50th percentile

3 = value at the 75th percentile

4 = value at the 100th percentile

ResultNumber

Quartile value.

Example:

  • QUARTILE(ARRAY(1,2,3,4,5), 3) → 4

STDEV

STDEV(A)

Calculates standard deviation, based on a sample population. For the entire population, use STDEVP.

ParameterTypeDescription

A

Array

Array of numbers values to be considered.

ResultNumber

Standard deviation.

Example:

STDEV(ARRAY(1,2,3))  ->  1

STDEVP

STDEVP(A)

Calculates standard deviation, based on the entire population. For a sample of the population, use STDEV.

ParameterTypeDescription

A

Array

Array of numbers values to be considered.

ResultNumber

Standard deviation.

Example:

STDEVP(ARRAY(1,2,3))  -> 0.8165

UMAX

UMAX(Value1, Value2, ...) 

UMAX(A)

Same as MAX, but does a universal comparison, accepting any type of values, including items and arrays. When used with multiple arguments, finds the largest value passed as parameters. When used with an array, finds the maximum value in the array.

ParameterTypeDescription

Value1, Value2, ..., ValueN

OR

A

Any


Array

Series of values.


Array of values to be considered.

ResultAny

Largest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. 

Examples: 

  • UMAX("aardvark", "zebra", "lion") → "zebra"
  • UMAX(ArrayofAnyTypes)

UMAX_BY

UMAX_BY(A, $)

Returns the maximum value by comparing all values in the array using the values calculated by calling $ for each element. Applies universal comparison to the value (UMAX).

ParameterTypeDescription

A

Array

Array of values to be considered.

$User Function
ResultAny

Maximum value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. 

Example: 

fixVersions.UMAX_BY($.releaseDate) → returns the latest fix version

UMIN

UMIN(Number1, Number2, ...) 

UMIN(A)

Same as MIN, but does a universal comparison, accepting any type of values, including entities. When used with multiple arguments, find the smallest value passed as parameters. When used with an array, finds the minimum value in the array.

ParameterTypeDescription

Number1, Number2, ..., NumberN

OR

A

Any


Array

Series of values.


Array of values to be considered.

ResultAny

Smallest value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. 

Examples: 

  • UMIN("aardvark", "zebra", "lion") → "aardvark"
  • UMIN(ArrayofAnyTypes)

UMIN_BY

UMIN_BY(A, $)

Returns the minimum value by comparing all values in the array using the values calculated by calling $ for each element. Applies universal comparison to the value (UMIN).

ParameterTypeDescription

A

Array

Array of values to be considered.

$User Function
ResultAny

Minimum value. If the array is empty, or all element are undefined, returns undefined.

Undefined values are ignored. 

Example:

fixVersions.UMIN_BY($.releaseDate)  returns the earliest fix version


Text Functions

Text functions let you manipulate character strings.

If a function expects a string but encounters a number, it converts it to a string using mathematical notation ("." decimal separator, no thousands separator).

CONCAT

CONCAT(Value; ...)

Concatenates (merges) text values into a single text. 

ParameterTypeDescription
Value1Value2, ..., ValueNText/JoinedText string(s). Accepts any number of arguments. Ignores falsy values.
ResultTextA single text containing all the values combined.

Example:

  • CONCAT(Reporter; ' => '; Assignee)

EXACT

EXACT(A; B)

Checks if text value A is exactly the same as text value B.

ParameterTypeDescription
AText/JoinedText value.
BText/JoinedText value.
ResultBooleanReturns true (1) if values are exactly the same. Otherwise, false (0).

This comparison is case sensitive, which is different from comparing A with B using an equals sign or text matching. Undefined values will be equal to each other and to empty strings.

Examples:

  • EXACT("Fox"; "fox") → 0
  • EXACT("Fox"; "Fox") → 1
  • EXACT(""; undefined) → 1

LEFT

LEFT(Value; N)

Returns up to N leftmost characters from a text value.

ParameterTypeDescription
ValueText/JoinedText to get characters from.
NInteger The number of characters to get.
ResultTextThe first N characters, starting from the left. If Value contains fewer characters, all of them are returned. If the value is less than zero, an empty text is returned.

Example:

  • LEFT("abc"; 2) → "ab"

LEN

LEN(Value)

Returns the number of characters in a text value.

ParameterTypeDescription
ValueText/JoinedText to count. If the value is not text, it is converted to text first.
ResultIntegerThe number of characters in Value.

Example:

  • LEN("abc") → 3

LOWER

LOWER(Value)

Converts text to lowercase. The locale of the current user is applied.

ParameterTypeDescription
ValueText/EachText to convert.
ResultTextValue in all lowercase.

Example:

  • LOWER("HAM") → "ham"

MATCH

MATCH(Value; Pattern)

Checks if the Value matches the Pattern.

ParameterTypeDescription
ValueText/JoinedValue to check.
PatternText/JoinedPattern to check against. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
ResultBoolean Returns true (1) or false (0).

Examples:

  • MATCH("Apples"; "Oranges") → 0
  • MATCH(" Blocker "; "blocker") → 1
  • MATCH("Hamster"; "ham*") → 1
  • MATCH("The Flight of the Bumblebee"; "/.light.*beer?/") → 1

MID

MID(Value; Index; Count)

Retrieves a part of the text.

ParameterTypeDescription
ValueText/JoinedThe text value to get a substring from.
IndexIntegerThe starting index of the part to retrieve, 1-based (first character is at index 1).
CountIntegerThe number of characters to retrieve.
ResultText Text containing Count number of characters, starting from Index.

Example:

  • MID("A quick brown fox"; 3; 5) → "quick"

REPEAT

REPEAT(Value; N)

Produces a text that is a repetition of the string value N times.

ParameterTypeDescription
ValueText/JoinedText to repeat.
NIntegerThe number of repetitions.
ResultTextThe repeated text.

Examples:

  • REPEAT("ha"; 3) → "hahaha"
  • REPEAT(123, 3) → "123123123"

REPLACE

REPLACE(Value; Pattern; Replacement)

Replaces all occurrences of Pattern with Replacement and returns the new text.

ParameterTypeDescription
ValueText/JoinedThe text to manipulate.
PatternTextPattern to find. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
Replacement (Optional)TextAn optional text to use instead of the matched parts. If omitted, the matched parts are removed.
ResultTextValue with replacements.

Examples:

  • REPLACE("I like cats"; "CAT"; "DOG") → "I like DOGs"
  • REPLACE("Can you read this?"; "/[aeuio]/") → "Cn y rd ths?"

REPLACE_AT

REPLACE_AT(Value; Index; Count; Replacement)

Replaces a specific part of the Value with Replacement text and returns the value.

ParameterTypeDescription
ValueText/JoinedThe text to manipulate.
IndexIntegerThe starting index of the part to replace, 1-based (first character is 1, second is 2, etc.)
CountIntegerThe number of characters to replace. When Count is 0, the Replacement string gets inserted at the Index position.
Replacement (Optional)TextAn optional text to use instead of the replaced part. If omitted, the part will be deleted.
ResultTextValue with replacements.

When the values of Index and Count are out of range, they are brought to the nearest sensible value.

Examples:

  • REPLACE_AT("A"; 1; 1; "B") → "B"
  • REPLACE_AT("What does the fox say?"; 6; 4; "did") → "What did the fox say?"
  • REPLACE_AT("A step for mankind"; 3; 0; "small ") → "A small step for mankind"
  • REPLACE_AT("A step for mankind"; 7; 1000) → "A step"

RIGHT

RIGHT(Value; N)

Returns up to N rightmost characters from a string value.

ParameterTypeDescription
ValueText/JoinedText to get characters from.
NIntegerThe number of characters to get.
ResultTextThe first N characters, starting from the right. If Value contains fewer characters, all of them are returned.

Example:

  • RIGHT("abc"; 2) → "bc"

SEARCH(Pattern; Value; Index)

Finds the first occurrence of a pattern in the value. 

ParameterTypeDescription
PatternTextThe text or pattern to look for. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
ValueText/JoinedThe text to search in.
Index (Optional)IntegerOptional parameter that provides an index to start searching at.
ResultIntegerReturns the index of the matched part (1-based), or undefined if not found.

Examples:

  • SEARCH("ham"; "The Ham is for the Hamster"; 6) → 20
  • SEARCH("Jedi*"; "Return of the Jedi") → 15
  • SEARCH("/^Jedi/"; "Not the Jedi you're looking for") → undefined

SPLIT

SPLIT(Value; Separator)

Produces an array from the value by splitting it using a separator.

ParameterTypeDescription
ValueText/JoinedThe text to split.
SeparatorTextThe text or pattern to split by. Can be an exact value, a wildcard expression or a regular expression. See Expr Pattern Matching for details.
ResultArrayReturns an array which contains the split texts.

Examples:

  • SPLIT("One, Two, Three", ",") → ARRAY("One", "Two", "Three")
  • SPLIT("A and B or C", "/ and | or /") → ARRAY("A", "B", "C")

SUBSTRING

SUBSTRING(Value; From; To)

Returns a substring, indicated by a starting index and ending index. Note that the indexes are 0-based, unlike in some other functions.

ParameterTypeDescription
ValueText/JoinedThe text to take the part from.
FromIntegerStarting index, inclusive, 0 means the first character, LEN(Value)-1 means the last character.
To (Optional)IntegerOptional ending index, exclusive - the character at this index will not be included. If omitted, the substring will include all characters up to the end of the Value.
ResultTextReturns the portion of the text contained between From and To.

If To value is greater than the text length, all characters will be included. If To is less than From, an empty text is returned.

Examples:

  • SUBSTRING("Batman"; 0; 3) → "Bat"
  • SUBSTRING("Batman"; 3) → "man"

TEXT

TEXT(Value)

Converts value to text. This function is rarely needed, because conversion to text happens automatically when needed.

ParameterTypeDescription
ValueAny (If text, Text/Joined)Value to convert
ResultTextValue converted to text.

Example:

  • TEXT(1.234) → "1.234"

TRIM

TRIM(Value)

Removes leading and trailing whitespace from the text.

ParameterTypeDescription
ValueText/EachThe text to manipulate.
ResultTextReturns Value without leading/trailing whitespace.

Example:

  • TRIM(" Batman ") → "Batman"

UPPER

UPPER(Value)

Converts the string to uppercase. The locale of the current user is applied.

ParameterTypeDescription
ValueText/EachThe text to manipulate.
ResultTextReturns Value in all uppercase.

Example:

  • UPPER("ham") → "HAM"

User-related Functions

USER_NAME

USER_NAME(UserKey) 

ParameterTypeDescription
UserKeyItem/EachUserKey value.
ResultTextUsername.

Returns the username specified by the user key.

USER_DISPLAY_NAME

USER_DISPLAY_NAME(UserKey)

Returns the user's full name, defined by the user key.

ParameterTypeDescription
UserKeyItem/EachUserKey value.
ResultTextUser's full name.

USER_EMAIL

USER_EMAIL(UserKey)

Returns the user's email, defined by the user key.

ParameterTypeDescription
UserKeyItem/EachUserKey value.
ResultTextUser's email address.

USER_IS_ACTIVE

USER_IS_ACTIVE(UserKey)

Returns 1 for active user, 0 for inactive user.

ParameterTypeDescription
UserKeyItem/EachUserKey value.
ResultNumber1 for active user, 0 for inactive user.




Miscellaneous Functions

ACCESS

ACCESS(Item, PropertyName)

Tries to access a property/field of the item. 

ParameterTypeDescription

Item

Item

Item containing the property or a key-value map.

PropertyNameTextProperty or field being accessed.
ResultAny

Value in PropertyName. Undefined if the Item is not an item or key-value map, or it does not have this property.

Example:

  • ACCESS(epic, "Story Points")

HISTORICAL_VALUE

HISTORICAL_VALUE(Item, Field, Date)

Show value of the field at specified date

ParameterTypeDescription

Item

Item

Issue item; use "this" for current row.

FieldTextField being accessed
DateDate/TimeDate or date/time value; use DATETIME function to convert into it: #DATETIME
ResultAnyField value at specified date (current value will be used for future dates)

Example:

  • HISTORICAL_VALUE(this, "cost", DATETIME("15/May/18 6:24 PM")) → 5.55
  • HISTORICAL_VALUE(this, "cost", NOW()) → 5.55

ME

ME()

Returns the user item of the current user.

Example:

  • IF(ME() = "admin"; "You're admin!")

URL_ENCODE

URL_ENCODE(Value)

Translates a text into application/x-www-form-urlencoded format.

ParameterTypeDescription
ValueTextValue to convert
ResultTextValue converted to application/x-www-form-urlencoded format.

Example:

URL_DECODE

URL_DECODE(Value)

Decodes an application/x-www-form-urlencoded text.

ParameterTypeDescription
ValueTextValue to convert
ResultTextDecoded Value.

Example:

JavaScript errors detected

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

If this problem persists, please contact our support.