A number of interesting metrics would have a formula that involves sums of other formulas. For example, to calculate the % of bugs in a sub-tree, you need to divide the total number of bugs (calculated as a formula) by the total number of issues (also calculated as a formula).

In the following example, we're going to recreate one of our bundled formulas, BugFix %, using temporary columns as variables.

1. Write the Main Formula

Create a new formula column and define the main formula. We're using total_bugs and total_issues as variable names, but they really could be anything.

In the Formula section, enter: If(total_issues > 0; total_bugs / total_issues)

A few things to note:

  • We don't have total_bugs and total_issues defined at the moment, so they are marked red. That's OK.
  • We use the IF() function to avoid dividing by zero.
  • We don't use the Sum option, because this is a ratio. The summing is going to happen when total_bugs and total_issues are calculated.

2. Create a Temporary Column with Total Number of Bugs

Next, we'll create a temporary column to calculate the total number of bugs.

In the Formula section, enter: IF (type='bug';1)

  • For an individual row, we use the IF() function to check whether the issue type is Bug. If so, the formula returns a 1 for that row.
  • The values are summed up (Sum over sub-items), with duplicate items excluded.
  • The type variable is automatically mapped to the Issue Type attribute.

3. Create a Temporary Column with Total Number of Issues

This time, we'll create another temporary column to calculate the total number of issues.

In the Formula section, enter: IF (itemtype='issue';1)

  • Note that we still have to use IF(), because items in the sub-tree could be folders or other non-issue types.
  • The values are again summed up (Sum over sub-items), with duplicate items excluded.

4. Define the Main Formula Variables

Now it's time to map the main formula's variables to the values calculated in the temporary columns:

  1. Go back to the main column configuration and click the total_issues variable.
  2. In the attribute selector, scroll down to the Used in Columns section and select the "Number of Issues" entry.
  3. Do the same to assign the total_bugs variable to the Number of Bugs entry.

Please note that if you're selecting an attribute defined in another column (from the "Used in Columns" section of the attribute selection drop-down), you copy the attribute definition from another column at that moment, rather than creating a link to that column. That means that even if the other column is removed or if its content is changed, the formula configuration will keep using the attribute as it was defined at the moment you configured the variable.

If you change the formula in the temporary column and want the change reflected in your main final formula, you will need to reassign the variable in the main formula.

5. Choose an Appropriate Format

Before we finish, we need to change the format to Percentage. And that's it - you've created an advanced formula!

As a final clean-up, you can remove the temporary columns from the grid. The Bugs % column has all the information it needs to do the calculation.