PluginCraft

Formulas

A formula column computes a value for each row from that row's fields and from the rows around it. Add one with Formula… in Add a column, and change it later with Edit formula… in its header menu.

On this page

The formula dialog

Field What it holds
Column name The name shown on the header, such as "Remaining points".
Formula The expression. Problems show as you type, pointing to the character where they occur.
Fields for the names in the formula The Jira field each name reads. A name that matches a field's id or name is filled in for you: story_points finds Story Points. For other names, you pick the field from a list.
Show values as Automatic, Number, Percent (0 to 1 shown as %), Duration (seconds shown as 1d 2h), Date, Text, or Markup (*bold*, _italic_ and [label|https://…] links).

The app checks the formula again when you save it, and refuses one that can't be parsed, calls an unknown function, or uses a name with no field.

The language

A formula is an expression. Names and functions are not case sensitive.

story_points * 2
IF(status = "Done", 1, 0)
summary.lower().left(10)
  • Numbers, text in double or single quotes (with \ to escape), true and false, and lists in square brackets.
  • Arithmetic with + - * /, and comparisons with = != < <= > >=.
  • AND, OR and NOT.
  • Comments with // to the end of the line, or between /* and */.
  • Chained calls: summary.lower() is the same as lower(summary).

Local names and functions

WITH gives a value a name for the rest of the formula, and can define a function of your own:

WITH rest = timeestimate : IF(rest > 0, timespent / (timespent + rest), undefined)
WITH half(x) = x / 2 : half(SUM{story_points})

Missing values and errors

A missing value makes arithmetic missing, so a sum with an empty field stays empty. Use IF_DEFINED or DEFAULT to supply a value. An error, such as a division by zero or a bad argument, passes through everything except IF_ERROR.

Names you can use

key and depth come from the row (the top level is depth 1). These names read the issue's fields without any setup: summary, status, status_category (To Do, In Progress or Done), type or issuetype, assignee, reporter, priority, resolution, created, updated, duedate, resolutiondate, timespent, timeestimate, timeoriginalestimate, labels, components, fixversions and project. Time fields are in seconds. You bind any other name to a field in the dialog.

User fields hold users, which show and compare by name. Two users are the same person when their account ids match. me() is the person viewing the page.

Reading other rows

Aggregations read the values of other rows. Write the function, any options after #, and the expression in braces:

SUM{story_points}
SUM#leaves{story_points}
JOIN#separator=" / "{key}
PERCENTILE#p=0.9{story_points}
SUM#preceding{story_points} + story_points

The functions are SUM, AVG, MIN, MAX, COUNT, JOIN, MEDIAN, UMIN, UMAX, PERCENTILE and QUARTILE.

By default an aggregation reads every row below. These options change which rows it reads and how:

Option Effect
#subtree The row itself and every row below it.
#children Only the rows directly below.
#leaves Only the rows at the bottom of the sub-tree.
#ancestors The rows above it.
#preceding The row's earlier siblings, for running totals.
#level=2..3 Only rows at those levels. The top level is 1.
#depth=1..2 Only rows that many levels below the row. Its children are 1.
#distinct Each issue once, however many rows show it.
#separator="…" The separator for JOIN. The default is a comma.
#ordering=asc or desc Sorts the values first. JOIN lists them in that order.
#compact Leaves out empty text as well as missing values.
#strict The result is missing when any value is missing.
#p=0.9 Which percentile, from 0 to 1. The default is 0.5.
#q=1 Which quartile, from 0 to 4. The default is 2.

A total still waiting for issues below it is marked incomplete until they load.

Queries in formulas

JQL{status = Done} is true when the row's issue matches the JQL query, with Jira answering as you. TQL{leaves} is true when the row matches the tree query. Put the query between the braces as is. A row shows "…" until its answer arrives, and a query Jira refuses shows Jira's reason.

IF(JQL{status = Done}, 1, 0)
COUNT{TQL{leaves}}

Functions

Group Functions
Arithmetic abs ceiling floor round sign sqrt pow exp ln log log10 mod
Logic if case choose and or not if_defined if_error is_error default
Text concat left right mid substring len lower upper trim replace replace_at repeat search match split join index_of last_index_of string number
Lists array map filter reduce sort sort_by reverse unique flatten merge_arrays subarray first last size contains contains_any contains_all is_empty sequence
Statistics sum avg average min max umin umax median percentile quartile stdev stdevp count
Dates and times now today date datetime make_date make_datetime date_add date_subtract date_set datetime_add datetime_subtract days_between hours_between months_between years_between weekday weeknum day month year hour minute second start_of_month end_of_month truncate_time truncate_to_day truncate_to_hour truncate_to_minute truncate_to_week truncate_to_month truncate_to_year format_datetime parse_datetime
Working days workdays_between add_workdays is_workday
Durations duration format_duration calendar_seconds calendar_minutes calendar_hours calendar_days calendar_weeks jira_minutes jira_hours jira_days jira_weeks
Users me user_display_name user_email user_name user_is_active
Other url_encode url_decode historical_value hierarchy_icon_url

Notes on some of them:

  • map(list, $ * 2) and filter(list, $ > 3) use $ for each item. reduce(list, 0, $acc + $) also has $acc, the value so far. sort_by(labels, len($), "desc") sorts by a key worked out for each item, and keeps the order of equal items.
  • sequence(1, 10) makes a list of numbers, with an optional step. The list can hold up to 10,000 numbers.
  • historical_value(status, "2026-10-01") is the value a field had at a moment, read from the issue's history. If the field hasn't changed since, you get the current value. For a moment before the issue existed, you get an empty value.
  • hierarchy_icon_url("issuetype") gives the address of the row's issue type icon. "priority" and "assignee" give the priority icon and the assignee's picture.
  • user_email returns an address only when Jira shows it to you. user_name is the account id, since Jira Cloud has no user names.
  • The working-day functions and the jira_ durations follow the site's working calendar: days off are skipped, and a day or a week is as long as the calendar says (8 hours and 5 days unless an admin changed it). The calendar_ durations use clock time.

Examples

Goal Formula
Remaining story points below a row SUM{IF(status_category != "Done", story_points, 0)}
Share of leaves that are done COUNT#leaves{IF(status_category = "Done", 1, undefined)} / COUNT#leaves{1}
Days until the due date days_between(today(), duedate)
Keys of the children JOIN#children#separator=", "{key}
Running total of estimates SUM#preceding{timeoriginalestimate} + timeoriginalestimate

Performance

Your browser works out formulas over the rows of the view, using the issue data the grid already has. With 2,000 rows or more, the work runs in the background so scrolling and typing stay quick. A very heavy formula stops with an error value instead of freezing the page.

In exports, formula values keep their kind: numbers, percentages, durations and dates stay numbers and dates in a spreadsheet.

Questions about the app? Write to info@plugincraft.ca.