Formula Column

Formula columns allow you to perform calculations or comparisons based on issue fields or other attributes, and display those results in a column.

Adding a formula column

Add a Formula Column

Start with adding a new column by clicking the + icon to the right of the column headings. Select Formula... as its type.

New column

Name Your Formula

In the Name field, give the column a meaningful name – something that expresses the purpose of your formula and will be easily recognized by yourself and anyone you might share it with.

Formula editor with the name field filled in

Formula

Enter your formula into the Formula field and assign any necessary variables.

Enter formula

See Formulas for more information.

Full Screen

Unable to render {include} The included page could not be found.

Options (Aggregation)

Select Sum over sub-items to have each row display an aggregate total, meaning the results for each row will be calculated as a sum of the values for that row and its sub-items.

sum over sub-items

When aggregation is enabled, you have a couple of options:

Exclude duplicates - If an item appears more than once in the structure, it's value will only be included once within the aggregate total.
After filtering - When checked, filtered items will not be included in the aggregate total. If this is left unchecked, the values of those items will be included in the calculation, even though they are not visible in the structure. Note: items filtered by a Filter Generator are always excluded.

You can also use aggregate functions to accomplish the same thing - or to create custom aggregations.

Sum over sub-items doesn't work for all formulas. For example, string values usually cannot be added together.

However, Structure has no way of knowing what each value represents, so these options are always available. When selecting this option, be careful to verify that the calculated values will make sense.

Format

The Format section allows you to customize the format of your results. The following options are available:

General - this default option will work for most formulas. If your results don't look right, try one of the others.
Number - lets you specify the number of decimal places that will always be shown. The value will be rounded up to the least meaningful digit in this format.
Percentage - treats the value as a ratio (0.0 = 0%, 1.0 = 100%) and adds a percent sign.
Date/Time - displays the results as date/time and allows you to pick the appropriate format.
Duration - displays duration values and allows you to pick an appropriate format: Basic (16h 40m), Days (0.69d), Hours (16.67h), Work Time (2d 40m), or Work Days (2.08d) .
Markdown - allows you to add custom formatting and more to a column. See Markdown in Formula Columns for details.

Going back to our sample formula, let's change the Format to Duration and check Work time.

duration

Our new column (Epic Under/Over Time) now displays the weeks, days, and hours that we are either ahead of schedule or behind schedule for each epic:

Structure with formula column

Note that dates, times and durations are all numbers in the Expr language.

Unless you select an appropriate format, duration is represented as the number of milliseconds. Dates are represented as "Epoch milliseconds", the number of milliseconds between midnight January 1st, 1970 (GMT) and the specified date, not counting leap seconds. Negative values are allowed to represent earlier dates.

Additional Information

Sharing Formula Columns

Formula columns are treated just like any other column, so they can be shared by:

Making them a part of a public or shared View, which other users can select
Sharing the structure via the Share button, which will open the structure with the same view. Note: the formula column must be saved as part of that view.

Sorting by Calculated Value

You can sort by the values calculated in a formula column by clicking the column header.