Get latest sprint end date from a group of stories

[Michael Byers]

Running latest version of Jira DataCenter.

This is a good starting point: How to update a story's target start and end dates based on the sprint dates using Jira Automation | Jira | Atlassian Documentation

But trying to get the latest sprint end date from a group of stories in an Epic. Reading a single date works, but adding the date.max does not work:  {{issue.sprint.last.endDate.jiraDate date.max}}

[Bill Sheboy]

Hi @Michael Byers -- Welcome to the Atlassian Community!

First thing, I am using Jira Cloud, not Server/Data Center, and so my answer is based on what I know about automation rules.

The Server/Data Center version has the ability to bulk-handle issues for a branch or scheduled trigger with JQL: https://confluence.atlassian.com/automation/run-a-rule-against-issues-in-bulk-993924653.html

When you do that, the {{issues}} smart value is available to try to find the latest (last) one.

Kind regards,
Bill

[Michael Byers]

Also tried with {{issues}} (which does return the set) but date.max does not seem to work with sprint dates.  For now, I added another IF comparison to keep the largest date in a temp variable.

[Bill Sheboy]

Perhaps also try these expressions to check the date without text formatting as jiraDate:

{{issues.sprint.last.endDate.max}} to check one sprint value per issue, or

{{issues.sprint.endDate.max}} to check all possible sprints for each issue (in case issues were in multiple sprints.

[Patrick red]

@Bill Sheboy  old thread but I got this to work on Jira Cloud with 

{{lookupIssues.sprint.endDate.first}} 

It's illogical but it brings the latest sprint date in a list of issues. 

You are a bit of a legend with these smart values so thought I'd share.

[Patrick red]

".jiraDate" is needed at the end to format the date for the Due date field.

[Bill Sheboy]

Hi @Patrick red 

How have you tested that expression?  What is in your lookup issues result?

The way I would interpret that one would be:

• for the results of the lookup as a set
• return all of the sprint field's end date values
  • This will be a list of lists (issues and sprints), returning something like this (with the jiraDate added), and assuming an arbitrary list of issues, some with / without sprints
  • [2024-10-01, 2024-10-29], [], [2024-08-23, 2024-10-01, 2024-10-29]
• and .first would return
  • [2024-10-01, 2024-10-29]
  • or this (The variation is from different Jira versions of automation.)
  • 2024-10-01, 2024-10-29

Context is key for scenarios like this, such as knowing which issues are selected in the lookup and how aligned they are for their sprints.  I recommend checking if your issues have been in multiple sprints.

Kind regards,
Bill

[Patrick red]

This is a great callout. The Lookup is producing a list of issues and then the "first" is pulling the first item in that list. Shucks. That just happened to be the "latest" date in the list too.

I can't seem to find a way to get that "latest" or "furthest out in the future" date.

Any ideas @Bill Sheboy ? 

With 

{{lookupIssues.sprint.endDate.jiraDate}}

I can get a list of all the Sprint end dates, but with 

{{lookupIssues.sprint.endDate.max.jiraDate}}

I get nothing in return.

[Bill Sheboy]

Again remember context: which issues are you selecting?

If all of the issues are in the current sprint, you only need to check one issue:

{{lookupIssues.first.sprint.endDate.max}}

If instead this is an arbitrary list of issues, some with and some without sprint values, they all need to be merged first, and then a maximum check performed.

• action: create variable, to store all the dates found, by iterating on the issues and inside iterating on their sprints, adding a comma after each one
  • name: varAllSprintEndDates
  • value: {{#lookupIssues}}{{#sprint}}{{endDate.jiraDate}},{{/}}{{/}}
• some action to get the latest one (i.e., maximum) 
  • {{varAllSprintEndDates.substringBeforeLast(",").split(",").toDate.max}}

[Patrick red]

What you created here works really well.

Just so I am understanding correctly, when the lookup is run, we are getting separate values and it's not actually a list, but with the variable we are creating an actual list which can be iterated over and a max/min can be pulled?

[Bill Sheboy]

The lookup issues result is a list of issues (0 up to 100 possible).  And each issue has several list fields, such as Sprint, Fix Versions, etc.  If we just looked at {{lookupIssues.sprint}} that would lead to the nested-list syntax I noted earlier.  When nested-lists are viewed as text, it adds the square brackets I noted earlier.

And so that created variable is performing two iterations to produce a comma-separated values (CSV) text string with a known delimiter: a comma.  Created variables are always text.  Then that variable is split() back into a list, and that may be used with list functions such as max.