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.

Array Functions

ALL

ALL(A, $)

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

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.

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.

Example:

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

COMPACT

COMPACT(A)

Removes all undefined values from the 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.

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.

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.

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).

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.

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). 

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.

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).

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.

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).

Example:

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

IS_ARRAY

IS_ARRAY(Value)

Returns true if the value is an array.

Examples:

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

IS_EMPTY

IS_EMPTY(A)

Returns true if the array is empty.

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.

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.

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. 

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.

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().

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. 

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.

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 $. 

Examples:

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

REVERSE

REVERSE(A)

Reverses the order of elements in the array.

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.

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.

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.

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.

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.

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.

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. 

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. 

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.

Examples:

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

DEFINED

DEFINED(Value)

Checks if the value is defined. 

Example:

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

DEFAULT

DEFAULT(Value; DefaultValue)

Substitutes DefaultValue if the Value is undefined.

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.

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.

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. 

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.

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. 

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".

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. 

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.


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.

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.

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.

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.

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).

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.

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.

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.

Example:

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

MINUTE

MINUTE(DateTime)

Returns the minutes in the specified date/time value.

Example:

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

MONTH

MONTH(DateTime)

Returns the month in the specified date/time value.

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.

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.

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.

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.

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. 

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.

Example:

• TODAY()

TRUNCATE_TIME

TRUNCATE_TIME(DateTime)

Removes the time value from the date/time.

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.

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.

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.

Example:

• TRUNCATE_TO_SECONDS(NOW())

WEEKDAY

WEEKDAY(DateTime)

Returns the number of the day of the week.

Example:

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

WEEKNUM

WEEKNUM(DateTime)

Returns the number of the week of the year.

Example:

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

YEAR

YEAR(DateTime)

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

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.

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. 

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.

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.

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.

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. 

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.

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.

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. 

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.

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.

Example:

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

Numeric Functions

ABS

ABS(Value)

Calculates the absolute value of a number.

Examples:

• ABS(5) → 5

• ABS(-4) → 4

CEILING

CEILING(Value; N)

Rounds value up to the N^th decimal 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 N^th decimal 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.

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'.

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.

MOD

MOD(A; N)

Returns 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. 

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.

Example:

• NUMBER("1.234") → 1.234

POW

POW(B; E)

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

Example:

• POW(3; 3) → 27

• POW(27; 1/3) → 3

ROUND

ROUND(Value, N)

Rounds value to the N^th decimal 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).

Examples:

• SIGN(123) → 1

• SIGN(0) → 0

• SIGN(-123) → -1

SQR

SQR(Value)

Returns the passed numerical value, squared.

Example:

•  SQR(5) → 25

SQRT

SQRT(Value)

Returns the square root of the passed numerical 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.

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. 

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.

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).

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.

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.

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).

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.

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.

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.

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).

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.

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).

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. 

Example:

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

EXACT

EXACT(A; B)

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

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.

Example:

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

LEN

LEN(Value)

Returns the number of characters in a text value.

Example:

• LEN("abc") → 3

LOWER

LOWER(Value)

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

Example:

• LOWER("HAM") → "ham"

MATCH

MATCH(Value; Pattern)

Checks if the Value matches the Pattern.

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.

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.

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.

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.

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.

Example:

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

SEARCH

SEARCH(Pattern; Value; Index)

Finds the first occurrence of a pattern in the value. 

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.

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.

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.

Example:

• TEXT(1.234) → "1.234"

TRIM

TRIM(Value)

Removes leading and trailing whitespace from the text.

Example:

• TRIM(" Batman ") → "Batman"

UPPER

UPPER(Value)

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

Example:

• UPPER("ham") → "HAM"

User-related Functions

USER_NAME

USER_NAME(UserKey) 

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.

USER_EMAIL

USER_EMAIL(UserKey)

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

USER_IS_ACTIVE

USER_IS_ACTIVE(UserKey)

Returns 1 for active user, 0 for inactive user.

Miscellaneous Functions

ACCESS

ACCESS(Item, PropertyName)

Tries to access a property/field of the item. 

Example:

• ACCESS(epic, "Story Points")

HISTORICAL_VALUE

HISTORICAL_VALUE(Item, Field, Date)

Show value of the field at specified date

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.

Example:

• URL_ENCODE("http://www.test.com") → "http%3A%2F%2Fwww.test.com"


URL_DECODE

URL_DECODE(Value)

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

Example:

• URL_DECODE("http%3A%2F%2Fwww.test.com") → "http://www.test.com/"