Jira structures Formulas

Hi Readers,

This is continuation of post on Jira Structures. Check out the post here to know the basics of Jira Structures.

Coming up more formulas for ready reference.

Formulas to use in Structures:

IF duedate < now() : DAYS_BETWEEN (duedate, NOW()) CONCAT “days late”

Roll up earliest start date to parent:

IF (COUNT #CHILDREN{0}, MIN #CHILDREN {START_DATE}, START DATE)

Roll up latest due date / end date of children to parent:

IF (COUNT #CHILDREN{0}, MIN #CHILDREN {dueDATE}, dueDATE)

Show overdue only on open issues:

Just change the highlighted fieldname based on requirement. we can check for baseline_start_date / baseline_end_date

IF(statuscategory!=”Done” AND baseline_end_date < today(), “Overdue”)

If a date in a custom field is older than 30 days from today. Just change the highlighted fieldname based on requirement.

IF DAYS_BETWEEN (today(), duedate) > 30: “It is older than 30 days”

To calculate duration between two dates for only working days

WITH raw days = days between (start, finish) +1 :

WITH weeks = floor(raw days + weekday (start)) / 7 :

raw days – weeks * 2

To convert String into Date format and check duration between two dates. If yes add spring idg.

IF(

baseline start date >= date (‘2024-01-15’) and baseline end date <= date (‘2024-01-26’) , “<SPRINT ID>”,

baseline_start_date >= date (‘2024-01-29′) and baseline_end_date <= date(’24-02-09’), “<SPRINT ID>”

}

Value match with JQL to show green check for positive result and red check mark for negative results

If (issuetype = “task” or issuetype = “story”) and status = “to do” and assignee != empty;

“✅”

if((issuetype = “task” or issuetype = “story”) and (status != “to do” or assignee = empty);

“❌”

))

Show iteration based on dates:

IF JQL{“START DATE” >= 2023-11-27 AND “start date” <= 2023-12-08) or (due >= 2023-11-27 AND due <= 2023-12-08)}:

“Iteration 1”

ELSE IF JQL{(“start date” >= 2023-12-11 and “start date” <= 2023-12-29) or (due >= 2023-12-11 and due <= 2023-12-29)}:

“Iteration2”

ELSE: “No Option”

Get the count of defects not in Withdrawn /closed linked to testcases

SUM {IF (JQL{ issuetype = defect and issuefunction in linkedIssuesOf(“issuetype = ‘test case'”) and status not in (Withdrawn, closed)} ;1;0)}

Get maximum status of children and show it in the parent level

IF(issuetype = “EPIC”, MAX#children{status})

Rollup total story points from child to parent

PARENT{SUM#CHILDREN{story_points}}

Rolllup average of all child value to Parent and Grandparent

IF(count#children{0} > 0):

SUM#subtree{<FIELD NAME>} / 100 / children

ELSE:

<FIELD_NAME>/100

To show output in percentage / Number / Duration based on the requirement.

To show the count of children in parent level

IF!issuetype and count#children{0}>0: count#children{0}

Time in status

Add new column with time in status

To get the total time spent in days

calendar_hours(timespent)

Stay tuned for more information on Jira Structures.