Using Issue Closed on Date in Jira Structure Formula

Zigi Fibert

I want to calculate the number of story points closed between 2 dates and used the number of team members to calculate velocity.

Something like (Sum the number of story points of issues closed between day-X and day Y and divide it by Z (number of people in the team).

Any help on how to achieve this will be welcome.

 

Regards

 

Zigi Fibert

Alessandro C_ _ALM Works_

Hello Zigi, Alessandro from ALM Works here.

You can actually easily calculate what you've been mentioning by building a new structure and getting into the board, issues from a defined period by using JQL.

From there, you can group the issues by assignee and use some formula columns to the math.

Note that this is a mere example I quickly built, Much likely, there are different approaches you can take with Structure to achieve the same result. That said, here's what you can do:

1. Create a new Structure and add some issues from a Period 

   Project = STR AND (status changed to Done during ("2020/11/09", "2020/11/12") )

   I limited my query to a project in this example, but you can do it with multiple projects if you'd like to.
   You can modify the timeframe in the "During" parenthesis as you need.

2. Group by "Assignee", then group by "Project"
3. Click on the "+" icon at the rightmost corner of the Columns row and look for "Σ Story Points".
   This is a formula bundled in Structure that automatically sums the Story points for you. (Make sure that at the columns properties, the 'Sum over sub-items' is marked)

At this point you should already have a structure showing the issues resolved on the specified period and how many Story Points have been accumulated per assignee.

The next step is to figure out, using structure, how many Assignees the structure you've built have:

1.  Add a new column, this time, look for "Formula...".
2. 'Name' it as you please and on the 'Formula' field write 'query' and then save (link located at the bottom right corner of the formula field) to create a new variable.
3. Click on the variable marked with the red indicator, right below the 'Formula' field.
4. Select "Query match" on the drop-down that will appear. This will bring up more options.
5. At the "Query Type" drop-down select S-JQL. Type the following query:

   child of root

   (This query will be used to dinamically count how many assignees appeared in your structure using the Structured JQL feature)
6. At the "Options" section, once again, make sure you check the 'Sum over sub-items' option.
7. Change the "Format" to 'Number' and click away from the Column panel to see the changes

Finally, create a new "Formula ..." field to calculate the average between assignee by story points:

1. Look for "Formula..." once again (inside '+" at the rightmost corner of the Columns row)
2. 'Name' the formula as you like. ('Velocity', as you mentioned, for example).
3. Add the following formula and save afterwards:

   IF(Type = Project; Story_Points + Resources; "")

   (If any of the fields appear in red at the Variables list, click on them and select the corresponding column)
4. Click away from the column properties to see the result.
   (As this time the calculation is intended to be done at Project level, there's no need to mark the 'Sum over sub-items')

Finally, you should be able to see the calculation you've mentioned in this new column.

(Note that, if you would like to, you can remove the columns created previously, as Structure stores the calculations used on the 'Velocity' column)

I hope this helps!

Best regards,
Alessandro C.