extract start date of a sprint from an array of sprint for an issue using smart values in automation

[Deloitte Mohajit]

Background: One of the performance efficiency metrics for a team is “Spill-over rate”

Definition: The average number of story points that have spilled over to future sprints in all the competed sprints in a given time range.

Formula: Total number of spilled over story points in a given time range / total number of completed sprints in a given time range

Requirement: In order to calculate the above metric, can I think of approaching this through an Automation by introducing a custom field (SpillOverStart)? 

Solution Approach:

Trigger: Issue field update (sprint)
Condition: If Issue.statusCategory = "In Progress" and {{issue.sprint.split(“,”).size}} > 1
Action: SpillOverStart = {{issue.sprint.split(“,”).get(1).startDate}} (start date of the 2nd sprint (index 1) in the array)

JQL to be used by the Scrum Master for computing this metric: (export the output to an csv and add up the total story points and divide it by the number of completed sprints)

project = <project_name> and issuetype not in (epic,sub-task) and SpillOverStart is not empty and (SpillOverStart >= "2023-01-01" and SpillOverStart <= "2023-03-31")

I am stuck with the above Action as it is NOT working out. Any leads on where I am going wrong or another way of achieving this will be highly appreciated.

[Deloitte Mohajit · Accepted solution]

Finally it worked. Two learnings:

1. Issue.statusCategory does not work as a JQL. So, I had to replace it with status not in (Done,"To Do")

2. split(“,”) is not working or may not be required. So, my Advanced compare condition looks like: {{issue.sprint.size}} > 1 and Action looks like: SpillOverStart = {{issue.sprint.get(1).startDate}}

[Justin Gong]

@Deloitte Mohajit  

Hi Deloitte!

I am trying to find a solution to getting the earliest start date and latest end date in an array of sprints. Your post seemed closest to what I am looking for so I was wondering if you knew how I can get that smart value to use in an automation.

For example, I am creating a rule where if an issue has multiple sprints ( {{issue.sprint.size}} > 1 ), edit the start and end date to be the earliest / latest in the array of sprints. 

[Trudy Claspill]

Hello @Deloitte Mohajit 

Welcome to the Atlassian community.

In what way is the automation not working?

What does the Audit Log contain when the rule executes?

Have you used the Log action to print out the smart values that you are using to see if they contain the values you expect?

[Deloitte Mohajit]

Thanks, Trudy for reaching out.

Automation audit log says "no action performed"

[Trudy Claspill]

That indicates that the rule was triggered, but the issue that triggered it did not meet the Conditions. Review the triggering issue, which is noted in the Audit Log, and the Conditions in your rule. If it doesn't meet the Conditions then the rule is working correctly, as per the instructions you gave it.

You can use the Log Action to print values into the audit log, like the smart values tou specified in tour conditions, to see the values the rule is getting from rhem

[Deloitte Mohajit]

Thank you, Trudy for your guidance! Appreciate it.