Jira Structure - Formula to display the min target end date from a group of Child tickets

[Tim Zhu]

Hi, 

I am using initiatives for my projects and I have child Epics that represent key milestones in my initiative.   I'm using the target end date to present when those Epic milestones should be completed.

I would like to create a structure formula column that shows the initiative's next milestone date by pulling from my open child Epic's min target end date.  The next target date would be the min target end date of any still open Epic.  (ie Closed epics mean the milestone has already passed/been completed.  Open epics would be upcoming milestones and the min function would pull the upcoming nearest milestone)

I believe the solution may be something similar to this answer.

[David Niro · Accepted solution]

Hello @Tim Zhu ,

Welcome to the Community!

For this, you can modify the issuetype set by the conditional function and use the aggregate function MIN{}.

Something like this should work for you:

IF issuetype = "Initiative":
    MIN#children{created}

 Let me know if this helps!

Best,
David

[Tim Zhu]

Thx @David Niro for the quick response.

How do I add logic to filter for only OPEN child tickets?

If issuesType = "Initiative": MIN#OpenChildrenTickets(or other JQL){target_end}

[David Niro]

Hi @Tim Zhu ,

Please try this:

IF issuetype = "Initiative":
    MIN#children{
        IF statuscategory != "DONE":
            target_end
    }

Instead of just returning the MIN{} of the children, it uses a conditional statement to define that it should only be using the dates from the issues that are not Done.

Bold Italics is the new piece.  You can adjust it if there other criteria you need met as well.

Hope this helps.

Best,
David

[Tim Zhu]

Excellent @David Niro , that worked! A couple of follow on questions:

1. I tried to use summary~"text" in the IF query.  It seems that text-based search isn't allowed.  Is this correct? Ie

IF issuetype = "Initiative":
    MIN#children{
        IF (statuscategory != "DONE" AND summary~'text'):
            target_end
    }

2. Do you know what the equivalent is for this that we can use in a Smart Filters - Rich Filter in a Jira Dashboard?  I basically would want to add this column to a View as well.

[David Niro]

Hello @Tim Zhu ,

That's great!  I'm glad to hear it is working.

1. For this you will want to use the SEARCH() function. Something like this:

IF issuetype = "Initiative":
    MIN#children{
        IF statuscategory != "DONE" AND SEARCH("your_text";Summary)!= undefined:
            target_end
    }

 2. Formula columns created in Structure are not available for use outside of Structure.   

That being said, you can create a Structure Gadget that would have the column available for use.

Additionally, you could (because it seems you are onprem) use an Attribute to Issue Field Effector to push the value to a custom issue field.  Then use that field in your other gadget.

Hope this helps!

Best,
David

[Tim Zhu]

Thx @David Niro  Answer accepted!  

For dashboards, is there a JQL we can write to pull the data instead of trying to pull the data from the structure formula? 

[David Niro]

Hi @Tim Zhu ,

Sorry, I'm not sure I understand the question.

Do you mean a query that can identify the MIN date of related issues and add it as a column?  Outside of Structure I do not believe it is possible. 

But you can create a Structure to have the exact view & calculations you would like and then add it as a Gadget to your Dashboard.

Best,
David

[Tim Zhu]

hi @David Niro hoping you can take a look at this question as it's related to this question which you answered successfully.