How do you set a Sprint Formula Column that shows incomplete issues in Backlog?

[Diana Gorv]

I have a structure that I export, and I need the sprint column to show the latest sprint, but if an issue was incomplete and wasn't assign to an open or future sprint, it will appear as text "Backlog".

For example, if I have issue ABC-123 that was In Progress and assigned to an open Sprint 2, it will show "Sprint 2". If ABC-123 was Done, then the sprint column will read as "Sprint 2". Then, I have ABC-456 also In Progress, but its last sprint was in a closed Sprint 1, so it will instead show "Backlog".

I tried using this Expr formula, but I can't seem to find the variable to get closedSprints:

IF(sprint = undefined or (statueCategory != Done AND sprint = closedSprint); "Backlog") or LAST(sprint.name)

I was hoping there was a way in formula that I don't have to update the sprint names or the Jira-generated sprint id number. How do I set the structure formula to achieve this?

[David Niro _Tempo_ · Accepted solution]

Hello @Diana Gorv ,

Something like this may work for you:

with _assigned = sprint.FILTER($.state = "active" OR $.state = "future"):

IF statuscategory != "DONE" AND _assigned.SIZE() > 0:
    _assigned.LAST()
 
ELSE:
    "Backlog"

It uses FILTER() to reduce the array of sprints to only those that are in an active or future state.  But we store it as a local variable for efficiency, since we use it more than once.

Next, an IF conditional function identifies the issues that are not Done and SIZE(), another array function, to identify if the new Array is > 0.  If it meets these criteria, it returns the LAST() sprint it was assigned to, and if it does not, it returns "backlog".

Please let me know if it helps!

Best,
David

[Diana Gorv]

@David Niro _Tempo_ Thanks for the quick response!

It's close, but now it looks like all Done issues appear as "Backlog". Can the formula use ELIF statements too? something like"

IF statuscategory != "DONE" AND _assigned.SIZE() > 0:
    _assigned.LAST()

ELIF statuscategory = "DONE":
   LAST(sprint.name)
 
ELSE:
    "Backlog"

I do see incompleted issues from previous sprints will show up as "Backlog", yay! 

I just need to get the complete issues get credit in their last sprint. 

[David Niro _Tempo_]

Hi @Diana Gorv ,

Sorry about that!  

Yes, but it is ELSE IF

ELSE IF statuscategory = "DONE":
   LAST(sprint.name)

[Diana Gorv]

@David Niro _Tempo_ You the man! It works! Thanks!!