Create filter for (TimeSpent + Remaining Estimate) > Original Estimate

[Kaja Iske]

Hi,

we want to evaluate issues where (TimeSpent + Remaining Estimate) > Original Estimate. Do you have any idea? I have found the value workratio but I don't think it is useful in this case.

Thank you!

[Trudy Claspill · Accepted solution]

Hello @Kaja Iske 

Are you using Jira Server or Jira Data Center? And, what version?

Jira does not natively support comparing one field to another in JQL.

It might be possible to address this through a third party plugin, or through use of the Automation for Jira feature depending on the version of Jira you are using.

Are you open to acquiring a third party plugin to satisfy this requirement?

[Kaja Iske]

Hi @Trudy Claspill , sorry for the late reply.

We're using Jira Server - v9.4.15

We do'nt want to use a third party plugin for that. Do you have an idea for using Automation?

[Trudy Claspill]

If you are using Jira Server (vs. Jira Data Center) the Automation plugin is not automatically installed.

Please find out if it is installed and what version you have.

If you do have it, the functionality will be available to Jira Administrators, and may be available to Project Administrators. Do you have either of those roles?

Documentation for the plugin can be found here:

https://confluence.atlassian.com/display/AUTOMATION/Jira+automation+-+Data+Center+and+Server

You have to make sure to choose the matching version in the upper right corner.

Note that there will be notes in the pages that say if the specific function is supported for "Server Lite". Since you are on Server, you would have a less-featured version of the plugin, and need to make note that not all features are supported.

We can help you figure out the rule to create after we know the details of the plugin version.

[Kaja Iske]

We have the plugin - Version 9.0.3

[Kaja Iske]

How can I activate the notficiations for this post? I don't get an email for your answers

[Trudy Claspill]

Click on your avatar in the upper right corner.

Select Manage Settings.

Select the Notifications tab.

In the first pull down in the tab select Settings.

That is where you find the general notifications settings for the community.

[Trudy Claspill]

If you are using Jira Server then you must be using the plugin Automation for Jira - Server Lite.

Unfortunately the functionality needed to achieve your goal is not available with that plugin. The functionality I'm thinking of is the Advanced Compare Condition that would allow you to compare the Timespent+RemaininEstimate total to the Original Estimate.

[Kaja Iske]

@Trudy Claspill You mean this? We have it :-)

[Trudy Claspill]

Yes, that is what I meant.

According to the documentation that is available only in the full Automation for Jira Data Center plugin. Perhaps you have Data Center rather than Server.

In that case, one way to solve the problem is by creating a Scheduled rule that will retrieve a list of issues, make the comparison, and end you an email. The basic structure of the rule would be:

Trigger: Scheduled
  set how often you want the rule to run
  add a JQL for the issue you want to review
    i.e.: project=X
Condition: Advanced Compare
  First value: {{issue.timetracking.timespent.plus(issue.timetracking.remainingestimate)}}
  Condition: greater than
  Second value: {{issue.originalEstimate}}
Action: Send Email

Related to the Trigger, there is a check box to determine if the rule will look at only issues that have changed since the last time the rule was run. You may want to set the rule up to do that, to reduce how many issues it looks at each time it runs.

Related to the Condition, I'm not positive those smart values will work in Jira DC. Those are the smart values that would be used in Jira Cloud.

That rule would send you an email for each issue that met the condition.

Another solution would be to use that same sort of rule to update a custom field in the issue to note that the Original Estimate is likely to be exceeded. Then you could create a second Scheduled rule that would use a Lookup Issues action to retrieve just the issues where that notation was made, rather than having a JQL in the trigger. By having the JQL in a Lookup Issues action instead of in the Scheduled Trigger it would be possible to send just one email listing all the issues.

[Kaja Iske]

Hi @Trudy Claspill ,

we do not want to use mail - so I had the idea to fill a new custom field with a marker and filter than for that field. Maybe it's not the perfect way but I wanted to test the logic first.

I've tried to use your example but it doesn't work. Do you know why?

[Trudy Claspill]

I recommend that you use the Log Action to write into your rule execution log file the values for each smart value you are using in the comparison:

{{issue.timetracking.timespent}}

{{issue.timetracking.remainingestimate}}

{{issue.originalEstimate}}

Use that to make sure that the information being used in the rule matches your expectation.

It is possible that I don't have the smart value names exactly correct. I don't have a Jira Data Center instance to use for testing.

[Kaja Iske]

@Trudy Claspill Sorry, I don't yet understand what you mean by using Log Action :-( Can you give me an example please?

[Trudy Claspill]

Refer to the product documentation that describes the available Actions:

https://confluence.atlassian.com/automation/jira-automation-actions-993924834.html

The Log action can be used to add message to the log entry created when an Automation rule runs. You can use it to print smart values and messages in the log file as a method to debug your rule.

[Kaja Iske]

Hi Trudy,

that helped me, thank you!

I then realized that it works best to calculate the times as simple numbers to fill my required field with 1 or 0. I would then look further :-).