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.
The following guide will walk you through how to create a formula in a formula column.
1. Add a Formula column
Select the + icon to the right of the column headings, Formula, and then New Formula...
2. 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.
3. Enter your formula
Formulas should be constructed using the Expr Language, a simple language that supports variables, arithmetic operations, and functions.
When you're finished, click Save.
As you begin typing, the formula editor will suggest functions, modifiers, and variables. An explanation for each function will also appear, describing what it does and how to use it. To scroll through the list of suggestions and learn more about another item, use the up and down arrow keys.
You can also hover over the code already in your formula to see how it works.
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.
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.
Learn more: Formatting Formula Columns
4. 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. 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.
If there are no issues, you're done! The results will appear next to the editor.
The example above is a simple formula to calculate whether or not we're on target to complete each epic on time:
IF type = "epic" :
originalEstimate - SUM#children { timeSpent + remainingEstimate }In case you're not yet fluent in Expr, we're telling Structure to:
Check whether the Issue Type is an epic: IF type = "epic" :
If so, add the Time Spent and Remaining Estimate for each child issue: { timeSpent + remainingEstimate }
Total that value for all the child issues: SUM#children
Subtract that total from the epic's Original Estimate: originalEstimate - ...
Handling Errors
Formula errors are typically due to one of the following:
Unmapped Variables - Structure attempts to map your variables to known attributes; however, depending on how you've named a variable, you may need to map it manually.
Syntax Error - occurs when Structure does not understand something in your formula. Often this is something as simple as missing a closing parenthesis or other punctuation. Review the formula and consult our Expr Language guide if you're unsure how to correct it. The part of the formula that failed to parse will be highlighted in red.
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 and Aggregate Function Reference to ensure you're using a supported function.
Expr Validation Error - occurs when there is a construct that is not allowed in a formula. Hover over the red highlighted part of the formula to see the error message. If multiple areas are highlighted, there are multiple errors to fix. Check the list of possible validation errors to learn more.
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.
Full Screen
The initial sizing for the formula editor is rather small. If you need more space, click Full-Screen to expand the window.
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.
Additional Resources