ALM Structure Filtered MIN/MAX dueDate and startDate values not finding min/max values

[Emily Russell]

Hello!  I'm trying to dynamically calculate the minimum start date and maximum due dates for certain issues in a subtree.  I've tried the following formulas and not received the expected minimum or maximum values.  Could you please advise what I'm doing wrong?  In the below screenshot:

I've created three columns:

1) The first called "Eng Execution Start," which should show the minimum start date of certain epics not part of the ABC Jira project (using JQL to filter):

MIN{IF(JQL {issueType="Epic" AND project!=ABC},format_datetime(startDate, "dd/MMM/yyyy"))}

This should show the earliest date from the Start Dates in the purple box in the "Eng Execution Start" column of the red box.  However, you can see from the screenshot, it displays one of the values from Start Date, just not the minimum one.  (The epics in the purple box are part of the DEF project, not the ABC project.)

2) The second called, "Eng Execution End," which should show the maximum due date of certain epics which are part of the DEF Jira project (decided not to use JQL to filter, for the sake of trying everything I could to get it to find the right one):

IF(itemtype = 'issue', MAX#SUBTREE{IF(issuetype = "Epic" AND key.filter($.match("*DEF*")),format_datetime(duedate, "dd/MMM/yyyy"))})

This should show the latest date from the Due Dates in the purple box in the "Eng Execution End" column of the red box.  However, it displays one of the values from Due Date, just not the maximum one.  (Those epics are part of the DEF project.)

3) Lastly, "Final Reqts / Designs" should show the maximum end date of certain tasks with "Product Requirements" in the Summary:

IF(itemtype = 'issue', MAX#SUBTREE{IF(issuetype = "Task" AND summary.filter($.match("*Product Requirements*")),format_datetime(duedate, "dd/MMM/yyyy"))})

 ...which actually works as expected as you can see from the green box, but there's only one date to choose from in a given subtree.

[David Niro · Accepted solution]

Hello @Emily Russell ,

Welcome to the Community!

Can you please try the following?:

1) "Eng Execution Start"

IF issuetype = "EPIC" AND project != "ABC":
    VALUES#children{startDate}.UMIN()

 2)"Eng Execution End"

with _def = key.FILTER($.MATCH("*DEF*")):

If issuetype = "EPIC" AND _def != undefined:
    VALUES#children{duedate}.UMAX()

3)

with _summary = summary.FILTER($.MATCH("*Product Requirements*")):

IF issuetype = "TASK" AND _summary != undefined:
    VALUES{duedate}.UMAX()

 the first two look at the items directly below the parent and create an array of their values.  chaining UMIN() and UMAX() returns only the lowest or highest value from those arrays.

the third formula looks at the children and the parent and does the same thing creating an array of the values and returning only the UMAX().

If you need values from issues below, you can change #children back to #subtree.

Please let me know if this helps!

Best,
David

[David Niro]

Sorry, forgot to also mention that you can select the Date/Time option (default is General) when creating your formula. 

This will save you the trouble of having to format the date/time in the calculation itself.

[Emily Russell]

Thank you so much, David!  UMIN and UMAX were exactly what I needed in this case; appreciate it!  One more level of complexity, though: I'd like the value to display on the parent, not the child.  So, in the example above, I want the UMIN start date and UMAX  due date of those ABC epics in the purple box to appear on the line with the red box, which is a user story in the DEF project.

I'm basically trying to put these three formulas into individual columns on the parent:

• If a child of this item is part of the ABC project and it is an Epic, show me the UMIN startDate value of all of the ABC Epics which are children of this item on the line of its parent.
• If a child of this item is part of the ABC project and it is an Epic, show me the UMAX dueDate value of all of the ABC Epics which are children of this item on the line of its parent.
• If a child of this item is part of the is a Task with the phrase "Product Requirements" in the summary, show me the UMAX dueDate value of all of Product Requirements Tasks which are children of this item on the line of its parent.

It's the part where it displays on the line of the parent that's currently tripping me up--I think I just need to read more on the usage of UMIN and UMAX with nested queries, but if you happen to have it handy, would love to know.

Thanks again, David-- you've sent me in the right direction with UMIN and UMAX!

[David Niro]

Hi @Emily Russell ,

You are very welcome!  I think I understand now.  The Epic is not the parent, it is the child in this scenario.  Thank you for the clarification! 

Do I also understand correctly that the Epics are part of project 'ABC'?  I initially thought they were not part of this project.  If I do understand this correctly let's try this:

1) "Eng Execution Start"

VALUES#children{
    IF issuetype = "EPIC" AND project = "ABC":
        startDate
}.UMIN()

Regarding 

• If a child of this item is part of the ABC project and it is an Epic, show me the UMAX dueDate value of all of the ABC Epics which are children of this item on the line of its parent.

There seems to have been some additional criteria previously.  specifically Filtering to find only issue keys that included "DEF".  Is this still required?  Would this be at the Epic or the parent level?

If it's no longer required, you can use the formula for 1) as a template, changing UMIN() to UMAX() and startDate to duedate.

3) "Final Reqts / Designs"

VALUES#children{
    IF issuetype = "TASK" AND summary.FILTER(
        $.MATCH("*Product Requirements*")):
        duedate
}.UMAX()

What these variations of the formulas do is ensure the children match the conditional criteria.  I had initially provided examples where the parents had to match the criteria.  This was an incorrect approach, since Epics were not the parents.

Please let me know if this helps.

Best,
David

[Emily Russell]

David, that's totally it!  Thank you so much!

(And for the record, you're absolutely right; I got my ABCs crossed with my DEFs in my response.  Thanks for working through it nonetheless!

[David Niro]

You are very welcome!  Glad to hear the solution works for you!!

[Emily Russell]

.