Iterate through issues, sum up their values and edit each of them

[Stefan Draber]

Hi all,

I’d need some help with an Automation and since I know that there are some Automation gurus around, I hope to find some support here 🙂

Business goal: each task in the backlog, which is already WSJF-scored (and thus also has estimated story points) should be updated with a forecast, in which sprint it will probably be worked on.

My idea: get a list of applicable issues with JQL, sorted by WSJF Score descending:

project = "AT2" and type = Task and status = Backlog AND WSJF IS NOT EMPTY ORDER BY cf[10258] DESC

While iterating through these issues, two things have to happen:

1. the story points (SP) of the current issue have to be added to the sum of the SP of all prior issues. Something like this: varStoryPoints = varStoryPoints + SP of current issue

2. depending on the actual sum of SP, a forecast-field in the current issue has to be updated. This would be like:

IF sum of SP <= 20 (capacity of one sprint)

THEN forecasted end date = end of current sprint + 2 weeks

ELSE IF sum of SP <= 40

THEN forecasted end date = end of current sprint + 4 weeks

ELSE IF sum of SP <= 60

THEN forecasted end date = end of current sprint + 6 weeks

…

When I tried to find out how to solve this, I got the feeling that is has somehow to be done with Issue Lookup and Advanced Branching. But to be honest, I didn’t quite get an idea how to use it in a useful manner 😕

Thus I’d be more than thankful for any guidance on solving this one!

Thanks in advance and best regards

Stefan

[Bill Sheboy · Accepted solution]

Hi @Stefan Draber 

Short answer: I do not believe this is solvable with an automation rule.  Other solution approaches are to investigate other planning tools or implement the issue updates outside of Jira, or an approximation rule (see below).

More information...(apologies in advance for the long response :^)

To do what you ask, the rule would need to process the issues in order, sequentially, accumulating the total forecast, accumulating the running total for a specific sprint's forecast, and update the forecasted sprint in each issue.  And, all of that would need to be repeated whenever an issue is added, moved, modified, completed, or deleted, in regards to any relevant issue fields (e.g., WSJF, story points, status, project, etc.).  This would take a non-trivial amount of time, and so if two (or more) issues updated in rapid succession, the processing would start colliding with itself.

Automation rule branches which could process more-than-one-thing occur in parallel and asynchronously.  This greatly improves rule performance, although it adds some complexity when thinking about rule design.  In fact, there is no guarantee of when such a branch will finish, up to the last step of the rule!

Branches are also limited to 100 issues maximum.  If there are more issues in the backlog than 100, iterative processing using scheduled triggers to "chunk" work would be required.  Such rules could still halt due to time-based processing limits.

Putting those things together, I do not know how a rule could be constructed that would do exactly what you asked...

However, a simple estimate of the number of potential sprints could be done (for fewer than 100 issues) by dividing your measures into your forecast: total size / average velocity = # of sprints.  This is what product owners often do when quickly reviewing a backlog to spot when an issue will be worked for a future sprint.

And a guess-timate version of that could be implemented in a rule, such as:

• trigger: some trigger
• action: lookup issues on JQL to find the issues in the backlog to update
• action: create a variable to save the total story points for the issues in the lookup results
• branch: on JQL to find the issues in the backlog to update
  • action: lookup issues for all of the backlog issues (meeting the above criteria) with a WSJF score greater-than-or-equal-to that of the branched to issue
  • action: using math, forecast the approximate relative-sprint by dividing the sub-total from the lookup issues result by velocity forecast, validated against the total from the created variable, and use that forecast to update the issue.

Honestly, I would consider other approaches before using that rule.  The new automation limits and the potential for collision errors may make it less valuable.

Kind regards,
Bill

[Stefan Draber]

Hi @Bill Sheboy 

first of all, thanks for your as always more-than-detailed answer. Actually you are one of Automation gurus I was thinking of when I shared my use case ;)

To do what you ask, the rule would need to process the issues in order, sequentially, accumulating the total forecast, accumulating the running total for a specific sprint's forecast, and update the forecasted sprint in each issue.  And, all of that would need to be repeated whenever an issue is added, moved, modified, completed, or deleted, in regards to any relevant issue fields (e.g., WSJF, story points, status, project, etc.).

I'm not sure if my requirements are actually that high. I think it's true that the processing has necessarily to happen sequentially and in the right order (from highest WSJF score to lowest). Is this basically possible with advanced branching? Or is the link you provided (parallel and asynchronously) telling me that exactly this is not possible?

The other thing, that it has to be triggered with every issue update on the relevant fields and the according risk of colliding processes, should not be required: the automation can be triggered scheduled, once a day, or manually, when the product owner thinks it's necessary or whatever. So, if this part is the showstopper, it can easily be ruled out I guess :)

Let's have a look at your suggested workaround:

However, a simple estimate of the number of potential sprints could be done (for fewer than 100 issues) by dividing your measures into your forecast: total size / average velocity = # of sprints.  This is what product owners often do when quickly reviewing a backlog to spot when an issue will be worked for a future sprint.

When I understand you correctly, then this would tell me for how many sprints I have work left in my backlog, right? But it would not tell me, whether the work item on backlog-position #27 will probably be pulled into sprint #3 or sprint #6, would it?

So, yes, a rough estimation based on something like average story points (sum of SP / velocity) would be sufficient. But it would still be required to assign each work item to the sprint-# based on its current WSJF-scored rank.

Thanks a lot for providing an idea of an according rule. I didn't make it running in the first try and don't know yet where I'm wrong. I'll try once again tomorrow and will let you know about the result.

Best regards,

Stefan

[Bill Sheboy]

Hi, Stefan!

Based on my understanding, there are two types of branches...

1. Branches which are on one-and-only-one-thing.  For example, branch on Parent, current issue, etc.  These are converted to run in-line, and so the order of rule steps is sequential.
2. Branches that could be on more-than-one-thing.  For example, all advanced branches, on linked issues, on JQL, etc.  Those are the parallel execution ones, and no processing ordering is known.  I recall there are backlog suggestions to add options for sequential processing, or at least "wait until branch done".

From your updates on the use case, the work-around rule I suggested could help.  It could run scheduled, once per day, and be forced to run on-demand, as needed.

What the rule would provide is a number for each issue, between 1 to N, for how many sprints in the future this issue might complete, assuming stable velocity, no backlog updates, etc.  With that number, you have several options:

• multiply it by your sprint length, add it to {{now}} and that is a forecast date
• use it to guess-timate the scheduled sprint
• etc.

Ultimately, this would just be another type of forecast.  The team and product owner are key participants in any forecasting beyond the time horizon of one sprint.

[Stefan Draber]

Hi @Bill Sheboy 

thanks for your clarification. Today I managed to get your idea running.

I created varSprintForecast with

{{#=}}1 + {{lookupIssues.Story Points.sum}} / 20{{/}}

and used the floor-function on the variable to update the forecast-field:

{{#=}}{{varSprintForecast.asNumber.floor}}{{/}}

This updates the issue with a number identifying in which sprint after the current it's being pulled in.

What I want to do next is to convert this number (1, 2, 3 ...) to the expected end date, which could be like:

end date of the current/active sprint + varSprintForecast.asNumber.floor * 2w).

Unfortunately I didn't find a way yet to extract the current sprints end date. {{sprint.endDate}} provides the end date of the sprint which is currently assigned to the branched issue, but that's not my current/active sprint. Do you have an idea on this one?

And what I just understood after implementing this automation: this isn't only a workaround but rather exactly what I wanted. It's not just a rough estimation based on average story size. Instead it counts up the sum of SP and draws a line after every 20 (sprint capacity). 

So, thanks a lot once again :)

Best regards,

Stefan

[Bill Sheboy]

Awesome; I am glad to learn that is helping.

Regarding the date forecasting, how about this:

• find the current active sprint before the branch, assuming it is the latest one
  • use lookup issues with JQL of
    • project = myProjectName AND sprint IN openSprints()
  • get the sprint end date from the first issue in the results
    • {{lookupIssues.first.sprint.endDate.max}}
  • save that date in a variable
• then use that date with the increment functions

[Stefan Draber]

Good morning Bill,

It was a bit fiddly to implement with that constant conversion between strings (variables), numbers and dates, but finally I got it running and it's doing exactly what it's supposed to be :)

So again, thank you very much for your patience guiding me through this challenge!

Best regards,

Stefan