Comparing Durations in Structure Formulas

[David Vaughan]

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. 

[Dionathan Lopes _ALM Works_ · Accepted solution]

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

[David Vaughan]

 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 

[Dionathan Lopes _ALM Works_]

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