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, we'll need to divide the total number of bugs (calculated as a formula) by the total number of issues (also calculated as a formula).
It is possible to do that with the Formula column, but it's a bit tricky. You will need to use temporary additional Formula columns. The following instruction walks you through the creation of the BugFix % column, mentioned above, so that you can solve similar tasks.
We're aiming to make this instruction obsolete in a future version, where we'll introduce hierarchy aggregates right into the Expr language.
Creating and Advanced Formula Column
(total number of bugs) / (total number of issues) .1. 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.
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
IF()function to avoid dividing by zero. - We don't use Sum option because this is a ration. The summing is going to happen when total_bugs and total_issues are calculated.
2. Temporary Column with Total Number of Bugs
Now let's define a new column that shows the value of total_bugs. That is quite easy with the formula column.
- For an individual row, we use
IF()function to check if the issue type is "Bug", and produce 1 as the result in that case. - The values are summed up, with duplicate items excluded.
- type variable is automatically mapped to the Issue Type attribute
3. Temporary Column with Total Number of Issues
In the manner we create another temporary column that shows total number of issues.
- Note that we still have to use
IF(),because items in the sub-tree could be folders or of other non-issue types.
4. Define Main Formula Variables
Now it's time to map the main formula's variables to the values calculated in the temporary columns.
Go back to the main column configuration and click total_issues variable. In the attribute selector, scroll down to Used in Columns section and select "Formula" entry with the name of the temporary column in parentheses.
Repeat the same for total_bugs.
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.
So if you need to change the formula in the temporary column, you can do it, but then you'll need to open the main formula configuration again and re-select the attribute for the variable.
5. Final Steps
Select Percent as the number format and it's all done!
As a final clean-up, you can remove the temporary columns from the grid. Our Bugs % column has all the information it needs to do the calculation.
