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

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