how to set the value of Epic Due Date with now plus the sum of time remaining of all child tickets

[Kent Chiu]

Hi,

I have difficulties setting the Epic's Due Date in Jira Cloud automation with the value of now plus the Epic's time remaining.

I tried to use the following syntax but they do not work:

{{now.plusDays({{issue.fields.timeestimate}}})}}

or

{{#=}} ({{now}} + {{issue.fields.timeestimate}})

That statement is within the For: Epic (parent) section:

What is the correct syntax to set the Epic Due Date with the current time (i.e. now) plus the Epic Time Remaining (i.e. SUM of the time remaining from the child tickets)?

Thank you for your help.

Regards,

Kent

[Trudy Claspill · Accepted solution]

At this location

...insert a Lookup Issues action.

Remaining Estimate is stored as seconds. So if you want to set Due date to now() plus the remaining estimate sum of the Epic's child issues (found in the Lookup Issues action) then use this smart value:

{{now.plusSeconds(lookupIssues.Remaining Estimate.sum)}}

[Bill Sheboy]

Hi @Kent Chiu 

Adding to Trudy's answer...

I recommend making the edits to Start Date and Due Date in one, single Edit Issue action.  That will be faster and reduce the chance of rule errors (due to timing problems).

After doing that, you could move the Lookup Issues action which Trudy suggests to above the new, single edit.

Kind regards,
Bill

[Kent Chiu]

Thank you Trudy for your help. I'll try your suggestion.

[Kent Chiu]

@Bill Sheboy , I am not sure what you meant by edit both Start Date and Due Date in one single edit.  How do we do it? I don't see the option in the Automation to do so. Could you please kindly advise? thank you.

[Trudy Claspill]

In one Edit action you can edit more than one field.

Just select additional fields to edit.

[Kent Chiu]

Oh wow, I didn't know that.  Thank you for the information. Learning more about Automation.

[Kent Chiu]

@Trudy Claspill , I was able to get some progress and get close to the end goal. At least it does not error out in Automation. Having said that, I see the Due Date field does not seem to get the correct value.

When I run the Automation, the Epic (Parent) issue Due date shows as 3 days from the Start date which is way off the total number shown from the JQL which shows as 8 days.

Where did I do wrong? Am I missing something?

Thank you.

Kent

After Automation run:

[Bill Sheboy]

Kent, would you please show an image of your current, complete rule, and of the details of the edit issue action?  Those may help explain this difference.  Thanks!

[Kent Chiu]

Hi  Bill, here is the complete Automation rule. Thank you for being so helpful.

[Trudy Claspill]

I suspect that the problem is that the calculation of the due date is not taking into consideration your configuration for working hours per day and working days where the display of Remaining Estimate does.

If your configuration is that there are 8 working hours per day and the Remaining Estimate is 8 hours then that will display as 1 day..

Let us say that you have a total of 16 hours in your remaining estimates. The calculation will do a simple addition of 16 hours (in seconds) to now, not 2 working days.

You will have to do more manipulation of the remaining estimates total to figure out how many working days the total represents and the add the value as days (or business days) to now.

[Bill Sheboy]

Yes, and...the behavior can be confirmed by writing that same value to the audit log just before (or after) those edits.  That will show the increment of {{now}} to the second, as opposed to the Start Date and Due Date fields, which are only dates.

One more thing...If this does not work, I recall a problem with using {{now}} as the first value in an increment expression.  We can come back to that if the adjustment Trudy suggests for dividing the seconds into days does not help.

[Kent Chiu]

Thank you @Trudy Claspill  and @Bill Sheboy , I think I found the right formula to get the number of days for the remaining estimate. The remaining estimate = Remaining estimate / (3600 * 8). I did some testing and experiment manually on the child tickets and it seems to work as I expected.

However, I don't know the right syntax to set in the Automation rule. I tried the followings:

1. {{now.plusDays({{lookupIssues.remaining estimate.sum}} / 28800)}

2. {{#=}} {{now.plusDays({{lookupIssues.remaining estimate.sum}} / 28800)}} {{/}}

3. {{#=}} {{now}} + ({{lookupIssues.remaining estimate.sum}} / 28800)} {{/}}

4.  {{#=}} ({{lookupIssues.remaining estimate.sum}} / 28800)} + {{now} {{/}}

None of them works. 

What is the correct statement to achieve that?

Thank you both again for your help.

--Kent

[Trudy Claspill]

I think it would be this, but I have not tested it.

{{now.plusBusinessDays(lookupIssues.Remaining Estimate.sum.divide(28800))}}

[Kent Chiu]

Hi @Trudy Claspill , you are a genius! It works!  I think I have achieved my automation.

Thank you so much to both @Trudy Claspill  and @Bill Sheboy for being so helpful in providing the information I needed to resolve my automation issues.

Have a nice weekend!

[Kent Chiu]

@Trudy Claspill , Sorry to bother you. Is there a way to lookup issues to exclude child tickets that don't have the remaining estimates (I.e, either empty or 0h)?

Due to the limitation of 100 issues per lookup issue, I am trying to reduce the number of the lookup issue to less than the limit.  Most of our Epics have more than 100 child issues.

I have tried different ways to add the condition:

1. "remainingestimate is not empty"

2. issue.fields.remainingestimate}} is not empty

on the lookup issue 
parent = {{issue.key}} and one of the above condition

Automation didn't take it.

I really appreciate your help.

Thanks,

Kent

[Trudy Claspill]

Hello @Kent Chiu 

In the "View all issues" search screen (under the Filters menu) this JQL is considered valid:

So in the Lookup Issues action JQL you should be able to use

parent = {{issue.key}} and remainingEstimate is not empty

If that is not working, please show us the error message or audit log or other evidence of how it is not working.

[Kent Chiu]

It is not working.  Here is the audit log.

I tried it in the production Epic. It has 166 issues. The ones that have remaining estimates are only 20 tickets.  However, with the additional condition, it didn't work. 

I created a child issue with no original estimates (i.e. 0 remaining estimates). The result of the run came up 0 day which should be 12.104 days in total from 20 issues.

Thank you for your help.

[Bill Sheboy]

I wonder...should the JQL test if the remaining estimate > 0 rather than just not empty?  A 0 value would appear as "not empty".

[Kent Chiu]

Hi @Bill Sheboy , Thanks for providing a suggestion. I tried that but it is still not working.

Here is the audit log:

Thank you for your help.

[Bill Sheboy]

Do you see the typo in the Lookup Issues JQL, where there is a space after the curly bracket?  That may be evaluating to parent = null...

Please try removing that extra space.

[Kent Chiu]

@Bill Sheboy , I corrected the typo but it seems to have no effect on the execution.

[Bill Sheboy]

Pausing for a moment, if you run a stand-alone query in View All Issues with the following, what do you observe:

parent = NG-443 AND remainingEstimate > 0

[Kent Chiu]

@Bill Sheboy , That JQL came up with 0 results.

I think I found the root cause of the issue. The reason it came up with 0 results is none of the Child tickets' original estimates have a value set. they are all empty (i.e. shoes as 0m)

The remaining estimate that I mentioned earlier (20 issues), they are all comes from the aggregated remaining estimate from the child ticket's subtask issues.

e.g.:

The question is we don't usually set the original estimate on the Child tickets. We keep all the time tracking on the Sub-tasks issues. Is there any way we can retrieve the aggregated remaining estimate of the child tickets that were derived from the sub-task issues?

Any suggestion?

Thank you.

[Trudy Claspill]

I'm a little confused myself in comparing the Audit Log output to the structure of the rule

Rule structure:

Trigger
If condition
  For Epic
    Lookup Issues
    Edit Epic
Else-If condition
  For Epic
    Lookup Issues
    Edit Epic
    Log information    

The log output indicates that the first condition is not being met by the trigger issue, but the following messages indicate the a Lookup Issues is being executed and finding no issues, and then the Epic is being edited anyhow. And then there is a Log action occurring, and then another Condition evaluated.

I note also that the you are using the JQL in two Lookup Issue actions, and the typo existed in both based on earlier images. Did you correct both?

Can you show us the entire rule as it currently exists and the entire Audit Log entry for an execution of the same rule.

[Kent Chiu]

Hi @Trudy Claspill , You are correct about the Rule Structure.  I think the issue is none of the child issues have the Original Estimate. They are all empty. The JQL in the lookup issue only picks up the child issues' Renaming estimate (it does not consider the aggregated remaining issues from its subtask).

I am completely lost now on how to update the Epic Due Date with the SUM of the child issues. We only track the time in the subtask tickets. We don't usually use the child issue's Original estimate. it is all derived from the child's issues time tracking.

Here is my automation rule:

Thank you.

[Kent Chiu]

@Trudy Claspill , Your rule structure is correct. I think the issue here is the query does not pick up any child issue because it does not have the Original Estimate value. Thus the remaining estimate is empty/0m.  If the child issues' Original Estimate is not empty is seems to work.

How can we get the Sum of the remaining (including the aggregate from the subtasks) the one highlighted in the red box?

Thank you.

[Trudy Claspill]

Hello @Kent Chiu 

The summation value you highlighted is not actually a field available in the issue that you can reference in Automation.

I have not worked through all the details, but I suspect that the only way to have access to that information is to actually copy the sum of subtask remaining estimates up to their parent issue.

However, if you sum up the remaining estimates on the subtasks and update the parent issue's Remaining Estimate field, then for the summation value you will get the Remaining Estimate sum from the subtasks plus the Remaining Estimate sum in the parent issue.

So, you would need to store the subtasks' remaining estimate sum in a separate custom field in the parent issue.

You would need a rule triggered by logging work on the subtasks, which would then recalculate the sum of the remaining estimates on all subtasks and update the custom field of the parent.

Then your existing rule would need to be changed to look at that custom field instead of the system field for Remaining Estimate.