The following article will walk you step-by-step through the creation of a basic formula.
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.
Name Your Formula
Open the Formula editor by clicking the downward-pointing triangle next to the new column header.
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.
Enter your formula into the Formula field and click Save.
Formulas should be constructed using the Expr Language, an intuitive language that supports variables, arithmetic operations and functions. You can find tutorials, reference guides, and several examples in our Expr Language documentation.
The example above is a simple formula to calculate whether or not we're on target to complete each epic on time:
In case you're not yet fluent in Expr, we're telling Structure to:
- Check whether the Issue Type is an epic:
- If so, add the Time Spent and Remaining Estimate:
- Total that value for the issue and all its children:
- Subtract that total from the epic's Original Estimate:
Check Your Formula
When you click Save, Structure will review your formula, attempt to link your variables to issue fields or other attributes and notify you of any errors. The results of your formula will also appear in the new column.
If the formula is ready to be used, a green mark is displayed. If it's not, the problematic parts are highlighted in the formula editor with red color.
Formula errors are typically due to one of the following:
- Syntax Error - occurs when the formula cannot be parsed. Chances are, you missed a closing parenthesis or other punctuation. Review the red highlighted sections and consult our Expr Language guide if you're unsure how to correct it.
- Function Resolution Error - occurs when the formula contains an unknown function. Review the formula and make sure the functions marked in red are spelled correctly. If so, check our Expr Function Reference to ensure you're using a supported function.
- Variable Resolution Error - occurs when you have used a variable that hasn't been defined yet. You don't have to correct these errors now. We'll address them when we define variables.
Handling Unexpected Results
In some cases, the formula may pass inspection, but the results aren't what you expected. You may simply need to edit your Variables, Options or Format; or you may need to revise the formula itself by clicking the Edit button.
Most formulas will contain at least one variable (otherwise, the result will be the same for each row in the structure). These variables need to be mapped to attributes, which can be issue fields, progress, a hierarchical total, user properties, another column or even another formula.
As you write your formula, Structure attempts to map your variables to well-known attributes. If Structure is unable to map your variables, you will receive an error and need to map the variable manually.
To map a variable – or to edit an existing mapping – click the variable's name in the variable list or in the formula and select the appropriate attribute from the drop-down list.
The following names are automatically recognized by Structure:
- Names of standard Jira fields, such as Summary or Priority.
- Names of custom fields, with all non-letters removed and all spaces converted to underscores. For example, Story_Points.
- Names starting with Total_ or Sum_ and having a well-known name afterwards, such as Sum_Story_Points or Total_Estimate. These are converted to a Sum attribute of the given value (without the duplicate removal option).
Even if Structure successfully maps your variables, it's still a good idea to review them!
To learn more about assigning variables within a formula, see Columns as Variables.
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.
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.
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.
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 as days, hours and minutes. You can also select Work time to display values using Jira's time tracking settings, so the duration reflects your work hours. See Work Time in Formula Columns for more information.
- Wiki Markup - allows you to add wiki markup, including colors and images, to a column. See Wiki Markup in Formula Columns for more details.
Going back to our sample formula, let's change the Format to Duration and check Work time.
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:
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.
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
- Creating a perspective URL that will open the structure with the same configuration, including the formula column
Sorting by Calculated Value
You can sort by the values calculated in a formula column by clicking the column header.