Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,366,831
Community Members
 
Community Events
168
Community Groups

Comparing Durations in Structure Formulas

Within a Structure report, I want a single column that highlights when issues have been either in a "to do" status for over 30 days in a "in progress status" for over 60 days. 

This is relatively simple to do with JQL as I can just use the changed operator, however I am relatively new to Structure so am struggling to figure out how this translates. 

I have figured how to use the time in status column to provide the duration that an issue has been in a status, although even this is not accurate as I am not interested in the cumulative total duration of time each issue has spent in the specific status. 

What I'm essentially after is something similar to the below, but I'm struggling with the correct syntax for comparing two durations: 
If (timeintodo > 29 days, "stuck in to do", timeinprogress > 59 days, "stuck in progress")

Any advice would be greatly appreciated. 

1 answer

1 accepted

0 votes
Answer accepted

Hello @David Vaughan 

Dionathan from ALM Works here.

Here are a few steps that you could try:

Step 1. Create the Formula Column

Formula example:

if (
status = "TO DO" and TimeInToDo > DURATION("29d"); "Stuck in To Do";
if (Status = "IN PROGRESS" and TimeInProgress > DURATION("59d"); "Stuck in progress")
)

Step 2. Map variable.

In the formula, you want to list all your statuses (TimeInToDo, TimeInProgress, ...) and after saving it, it will give errors, then you need to map to customizable attribute > time in status.... each variable by selecting the appropriate status from the dropdown list.

Please note that this formula will work only on the latest versions of Structure (V7.0 +). 

You can also find more information about Duration formulas here = https://wiki.almworks.com/documentation/structure/duration-functions-115289497.html#DurationFunctions-durationDURATION and more information about the "Time in Status" column here - https://wiki.almworks.com/documentation/structure/time-in-status-column-71042417.html

Please let me know if it helps.

Best Regards,
Dionathan L

 Thank you Dionathan! This has certainly put me on the right track. 

A couple of follow up questions which I couldn't see that answers to in the provided documentation: 

1. Currently, my TimeInToDo and TimeInProgress fields display a cumulative total of all time issues are spent in that state. Is there anyway to amend this to only show the time spent in this status since it was last changed? I have tried enabling Returns Only but this does not suit the requirement as because To Do is my initial status and therefore has not returned from anywhere, it returns 0m. Also, if my issues have only been in To Do or In Progress once, it also states 0m, which is not ideal. 

2. I am having an issues with a separate formula column. I am trying to highlight issues without a Treatment Plan provided (multi-line text field) but it is not returning any results even if the field is indeed blank. The formula i am using is "TREATMENT PLAN" = "", with treatment plan being linked as a variable to the treatment plan jira field. Is this correct and if so what am I doing wrong? 

Kind Regards, 
David 

Like # people like this

Hello @David Vaughan 

Thanks for letting me know.

Please reach out to us directly at support.almworks.com and we'll take a detailed look at your set-up, providing a proper support.

Looking forward to hearing from you.

Best Regards,
Dionathan L

Like Dave Rosenlund _Tempo_ likes this

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events