Formula to find longest pole if all subtasks have target date and undefined otherwise.

[Surekha Gupta]

Hi,

Is there a way to rollup to undefined target date if one of the subtasks being rolled up also has undefined target date (Currently I use a custom date field for Gantt finish date, and use Gantt finish date for auto rollup ).

See snapshot.

Rollup shows 8/31 projected Code Complete date for initiative, since a couple tasks which haven't defined code complete Target date are defaulting to "project start date" . I want to write a formula for rollup that will result in undefined target date  if one of the child tasks has an undefined target date. Is this possible?

In other words, is there a way to "iterate over the subtasks" i a structure formula? I was able to create a new column in my structure that blanks out the CCTD, if it is undefined. 

Here is the formula I use that works for leaves: 

IF (DEFINED (CCTD); CodeCompleteTargetDate; DATE(Undefined))

Pseudocode for what I want to calculate for parent node is  as below:

---

For (all subtasks for the current row){

      if subtask CCTD is not defined

                parent CCTD = undefined, 

                 exit and goto end 

}

parent CCTD = MAX(subtasks.CCTD)

end

---

end result I wish is that my parent initiative should not have the code complete date set to 8/31/23, but it should be blank.

[Surekha Gupta]

HI Stepan, the above solution is neat and it works, but I realized that I don't want to set the Parent's Code complete target date to empty(TBD) unless the child with missing CCTD is in progress/backlog states. I modified as follows:

if sum#strict{if !codecompletetargetdate and JQL{status in (New, "To Do", Backlog, "In progress")}: 1} > 0: " "
else
max{codecompletetargetdate}

But I believe that is incorrect since I am trying to do logical AND operation on a true/false value and a filter result. I probably need to do ".FILTER" followed by aggregation, but not sure how to do that.

[Stepan Kholodov _Tempo_]

Hello @Surekha Gupta 

If you want to:
- check the dates in a given field of sub-issues and
- for each parent to show either the latest date in the field or no value if there is at least one sub-issue with no value in the field,

you can try a formula like this:

if sum#strict{if !codecompletetargetdate: 1} > 0: " " else
max#strict{codecompletetargetdate}


The CodeCompleteTargetDate should be the field in question with dates.

I hope this helps. If you need further assistance, please reach out to us directly at our support portal and we'll look deeper into your setup and requirements.

Best regards,
Stepan Kholodov
Tempo

[Surekha Gupta]

HI Stepan, the above solution is neat and it works to some extent, but I realized that I don't want to set the Parent's Code complete target date to empty(TBD) unless the child with missing CCTD is in progress/backlog states ( i want to ignore completed tasks with blank CCTD). I modified as follows:

/* if any of the descendants in active development don't have a valid CCTD, set the parent CCTD to undefined * else roll up the longest pole among the descendant issues, including self */

if sum#strict{ if JQL{status in (New, "To Do", Backlog, "In progress") AND "Code Complete Target Date" is EMPTY}: 1} > 0: " " else: max{codecompletetargetdate}

and also  this alternate form

if sum#strict{
if !codecompletetargetdate:
if JQL{ status in ("To do", New, Backlog, "In Progress")}:
1} > 0: " " else
max{codecompletetargetdate}

but in both the cases it seemed like the aggregation of latest CCTD was only happening for the immediate parent, and the second level parent always had CCTD undefined. To me it looked about the way the descendants are traversed, that the second level parent was already setting it's CCTD to empty if the first level was empty before traversing to the first level parent who was correctly calculating the CCTD by aggregating max value among children.

See attached snapshot.

Note: What I am trying to do is a "Gantt style roll up" except I need to do it for two different dates, code complete date and the end date(that includes verification), but I can set my Gantt config to use only one of those dates. Also, I don't like the fact that Gantt sets the undefined dates to project start date for roll up purposes and there is no way to flag that some dates are missing.