Using Structure formula to calculate the avg. velocity

[Anh Thi]

Hi, 

I was wondering if it was possible to calculate the avg. velocity during the past 3 sprints, using a formula in Jira structure.

Thank you.

[Mukund Iyer Rajamony]

I want to use the Structure for JIRA like a velocity chart for more sprints than the regular canned velocity chart for 7 sprints.

So my structure deploys a query like this:
project = "xxx" and type in (Story,Improvement, bug) and status in (Closed,Resolved,Done)

I group this sprint-wise and set up a column (sum story points) -> the column displays data that is a bit faulty.

Some of the stories spill over into 1 or more sprints and hence show up more than once. I want those stories to show up only in the sprint in which they were actually closed.... Any help would be greatly appreciated.

[Stepan Kholodov _ALM Works_]

Hello Mukund,

Could you please clarify - do you want to exclude issues from multiple Sprints and only show them in Closed Sprints, or you only wish to count Story Points of such issues once in their Closed Sprints(and not in other Sprints where they are also present)? If it’s the latter than I’m afraid there is no straight-forward way of doing this since there is no possibility to show Story Points only for issues in closed Sprints.

If you select the ‘Exclude duplicates’ option in the Story Points column, Story Points of issues in multiple Sprints will only be calculated once.

For more precise count in each Sprint with the exclusion of Story Points of issues in Closed Sprints, you can use two columns instead of one Story Points total column: 

- somehow mark all issues that are located in Closed Sprints. It can be a some text value in Jira field - Labels, for example.

- then you can add a Formula column and use formula like this in it: if(issuetype and labels!="used text indicator",story_points). Then you can sum over sub-items in this column and aggregate the values.

- And you can add the second Formula column with a similar formula to only count Story Points of marked issues in Closed Sprints.

I hope this helps. If you need further assistance, please feel free to reach out to us via support.almworks.com and we’ll gladly look into your use-case closer.

Best regards,

Stepan Kholodov

ALM Works

[Mukund Iyer Rajamony]

Hi Stepan,

The intent was in being able to produce a velocity chart for the project through all the sprints.

Currently, JIRA only produces the last 7 sprints. However, we do feel the need to be able to track velocity in the history. This helps determine a team-wise metric and further helps in planning.

In order to do this, one needs to know clearly which story was changed to closed in which sprint exactly. The same for bugs.

[Stepan Kholodov _ALM Works_]

Hello,

Could you please provide more details about what exactly would be a good parameter to show velocity in your case? If you want to know, for example, how long it took issues in the last 3 Sprints to transition from Open/To Do status to be completed, the calculation is possible with the Formula column or with the recently released Time in Status column. Individual values of issues can be aggregated from bottom levels to the top, providing overall average time for parent issues/Sprints.

If it would be more convenient, please contact us at support.almworks.com and we will get back to you shortly. We could take a look on your setup and requirements, and recommend a most fitting solution for you.

Best regards,

Stepan Kholodov

ALM Works

[Anh Thi]

Hi,

I want to sum up all issues that are done in the last 3 sprints and take the avg of it in terms of points. I do not want to hardcode the name of the spints. I just want the last 3 spints  every time i open my structure. Thank you.

[Stepan Kholodov _ALM Works_]

Thank you for sharing more details.

It is possible to calculate an average number of Story Points per Sprints or Several Sprints(for instance, using a simple AVG{Story_Points} formula in the Formula column), but doing it only for three last Sprints is too complicated of a condition. For this to be possible, there should be a possibility to find issues from these three Sprints in Jira without specifying their names, i.e. it should be possible to write a query which will return only issues from last there Sprints and build a structure based on this query.

Without it, you can, for example, calculate average points for issues in all completed Sprints or calculate it for the last completed Sprint.

I hope this helps. If you need assistance with setting either of the described solutions, please feel free to create a ticket at our Service Desk for proper support.

Best regards,

Stepan Kholodov

[Anh Thi]

Thank  you for your quick reply. 

I have another question, I am using the format Wiki Markup. But I want to format my output that i am displaying on the screen in %. How do I do it?

WITH ATN = SUM#LEAVES{IF(DATE_SUBTRACT(NOW(),7,"day") >= CREATED; COUNT#LEAVES{ISSUETYPE})} :

IF(ATN > 40; CONCAT("{panel:bgColor=#F38F07}", ATN, "{panel}"); CONCAT("{COLOR:BLACK}", ATN, "{COLOR}"))

[Stepan Kholodov _ALM Works_]

Hello,

Could you please clarify - which value do you want to see in percentages? If it's the 'ATN' value, then you can just the symbol to this part:

("{panel:bgColor=#F38F07}", ATN, "%", "{panel}")

Or you want to show a percentage of ATN value's relation to the overall number of sub-issues? In this case, you would need to add:

("{panel:bgColor=#F38F07}", round(ATN/count#leaves{issuetype},2)*100,"%", "{panel}")

I hope this helps!

Best regards,
Stepan Kholodov

[Anh Thi]

It workes, thank you very much.