Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Apps & Integrations

Send an Email or Publish to Confluence - What should you do with your release notes?

Background When you hear the words ‘Release notes’, almost always you think of an unsolicited email from a software vendor. But I am here to tell you that from our data, sending release notes via E...

131 views 1 2
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you