Is there a way to filter the value of Time log minus (-) original estimate or vice versa

[Sai kiran P]

Hi Everyone,

I have a requirement to filter the value of the Time log minus (-) original estimate or vice versa. 

Scenario: 

Let's say the Original estimate is 4 days and I have logged the work for 5 days and I need to return the value in a field with the value 1 day ( time log - original estimate). 

Please let me know if there are any alternatives to achieve this.

Note: We are hosted on Cloud instance and not using any Add-ons

Thank you in advance

[Trudy Claspill]

Hello @Sai kiran P 

There is a field called remainingEstimate that is set each time Time is logged. Does that fulfill your need?

[Rachel Wright]

Also a good potential solution. The "remaining estimate" field simply returns "0" when the time logged exceeds the estimate entered. And of course issues with time logs and no time remaining is different than issues without time logs (meaning the remaining estimate field is null.)  Ex:

JQL: remainingEstimate = "0"

vs.

JQL: remainingEstimate is empty

For what it's worth, if you want to access the "remaining estimate" field using an automation smart value, use: {{issue.remaining estimate}}

Fun stuff!

Rachel Wright
Author, Jira Strategy Admin Workbook

[Sai kiran P]

Thanks for your response

[Rachel Wright]

Hi @Sai kiran P ,

I have an idea for you. Try using Jira Cloud's built-in automation feature to do the calculation and then filter over estimated and under estimated issues by the calculated result. 

How to Do It

Create an automation to periodically subtract the original estimate from the time spent. Then either dump the response into a custom number field or flag the issue in some way. (Ex: add a label named "under-estimated" or "over-estimated") Then you can query/filter/report on the response, label, or whatever way you used to identify the issues you're looking for.

The smart tags (variables) you'll need to use are:

• {{issue.timeSpent.divide(3600)}}
• {{issue.original estimate.divide(3600)

Here's the formula I used. Note: For math functions, prepend {{#=}} to open the calculation and append {{/}} to close it.

• {{#=}}{{issue.timeSpent.divide(3600)}}-{{issue.original estimate.divide(3600)}}{{/}}

In this formula, I've converted both values to seconds, using "divide(3600)".

Implementation Example

I created a simple (manual) automation, that just logs the result of the calculation, to test this idea. Here's what my super simple automation looks like:

Here's my sample Jira issue with an original estimate of 4 days and a worklog of 5 days. Note: Jira shows 5 days as 40h. (5d x 8h (my application's default) = 40h)

And there's the result showing the number written to the log.

In the above screenshot, the difference between 4d and 5d is "8" meaning and excess of 8 hours over the estimate. If there was a surplus of 1d, the result would show as "-8".

If this isn't what you're looking for, I hope it leads you to a final solution!

Rachel Wright
Author, Jira Strategy Admin Workbook

[Sai kiran P]

Hi @Rachel Wright

Thanks for your response.

This is what I'm exactly looking for, Can you help me with the configuration?

I want this to result in a custom field, How do I configure it can you please help?

Thanks in advance!

[Rachel Wright]

Woohoo! Glad to hear it @Sai kiran P .

• Start by logging in as an application administrator and creating a new custom number field. Visit: Admin > Issues > Custom fields
• Then, add the new custom field to the "edit" (or shared) screen(s) in your project. Visit: Admin > Images > Screens
• Finally, create an automation to check for work logged, do the calculation, and put the result in the new custom field. Visit: Project Settings > Automation

Notes and considerations:

• First, decide how often the calculation should be performed. In my example, I've chosen to look for changes in work logged. You could also do it on a schedule, like once at the end of each day.
• Sometimes it takes some time for the automation feature to recognize new custom fields. So go get some coffee and give it a while.

Automation rule specifics:

Here's how I set up my automation.

• Trigger: Work logged
  • For: All worklog operations
• Action: Edit issue
  • Choose fields to set: [your custom number field]
    • Then click the ellipses ("...") next to the empty text field and choose "SET"
    • In the empty text field, enter the desired calculation.

Here's the calculation I used: {{#=}}{{issue.timeSpent.divide(3600)}}-{{issue.original estimate.divide(3600)}}{{/}}

Here's what my completed rule looks like:

Additionally, I chose to post the calculated result to the log, just so I can check my math. The log action is is shown in the above screenshot and the result is shown in my Nov 29 message.

Hope this helps!

Rachel Wright
Author, Jira Strategy Admin Workbook