Need a Calculated date field based on two other Custom fields and status of the issue.

 

For a particular Assignee:

I want his Queue to display the issues as follows:

All the issues in the status "In Progress" should be Displayed first in the queue and sorted in "Priority"(custom number field) Descending order.

After the "In Progress" tickets, I need all other tickets in any status sorted based on the "Priority"(custom number field) Descending Order.

Conditions and fields used to calculate the required Field:

Custom Fields that will be used:

"Complexity" - Custom Number field in hours
Ex: 1 or 1.5 or 2.25

"Actual Due Date" - Custom Date Field

"Calculated Due Date" - Custom Date Field

To be calculated:

For first ticket:

Actual Due Date = Today + Complexity

Calculated due date = today + Complexity + 3 days.

For second ticket, the

Actual Due Date = Actual Due Date of first ticket + Complexity of current ticket

Calculated due date = Actual Due Date of first ticket + Complexity of current ticket + 3 days.

For the Third ticket:

Actual Due Date = Actual Due Date of second ticket + Complexity

Calculated due date = Actual Due Date of second ticket + Complexity + 3 days.

and so on...

And if a ticket comes in, Based on the status and priority it should be inserted in the queue and the Calculated Due Date field should be calculated based on the previous tickets avaliable in his queue and the Calculated Due Date should be Re-Calculated.

Any help appreciated. Thanks in Advance!

 

2 answers

0 vote
Gaston Valente Community Champion Sep 04, 2017

Bunny,

Check if you can achieve that with this free plugin:

https://marketplace.atlassian.com/plugins/com.innovalog.jmcf.jira-misc-custom-fields/server/overview

I'm using it to do simple calculations based on fields.

hi, Gaston,
I also use this addon, however, my formula for calculated date/time doesn't work. (it only work if I don't add the date##).

 

would you share your formula with me for Custom Date -60 ?

Mine is

<!-- @@Formula:(issue.get("customfield_10805") == null ? null : issue.get("customfield_10805")-60) --

<!-- @@ format: DATE  -->

 

Thank you.

Sonya, according to the documentation the value if not null, is an instance of java Date.

so, the best way to go is by convert to timestamp and then do the calculations:

60 days is equal to 60 days * 24 hours * 60 minutes * 60 seconds = 5.184.000

issue.get("customfield_10805").getTime() - 5.184.000 is the target timestamp, then you need to convert it back to Date with settime and thats all

Hi ,Gaston, 

thank you, however, it still doesn't work in my end. 

 

I've tried to use below setting, neither of them work >"<

<!-- @@Formula: (issue.get(“customfield_10805”).getTime() +5184000000) -->

<!-- @@Formula: (issue.get(“customfield_10805”).getTime() +5184000) -->

<!-- @@Formula: 
new Date(issue.get(“customfield_10805”).getTime() - 21*24*60*60*1000)
-->

<!-- @@Formula: 
new Date(issue.get(“customfield_10805”).getTime() - 21*24*60*60)
-->

 

the only work in my end is , but still display in date time format

<!-- @@Formula:issue.get("customfield_10805") -->

<!-- @@ Format: DATE_PICKER -->

Thomas I'm New Here May 04, 2018

Hi Sonya,

I don't know if you solved your issue, but below you have the way I solved it :

<!-- @@Formula: (issue.get("customfield_10805")==null ? null : org.apache.commons.lang.time.DateUtils.addDays(issue.get("customfield_10805"), 21)) -->

 

Regards,

Hey Bhanu/Bunny!

I can provide you some pseudo code to model your own Script Fields after, but personally I don't recommend this set-up. The problem here is that, unless you have some other means of connecting the issues, you'll have to do a JQL query for each of the Script Fields, which will be calculated on every issue and could potentially turn into a performance nightmare. But, regardless, I would do the following to accomplish this:

First, to get all of the issues that are "In progress" to show up above all other issues. There is no built-in JQL that I know of that will allow you to do this. Instead, what you could do is create a Script field that returns a 1 if the issue is "In Progress" and a 0 if it's not. It's not pretty, but if you order the issues based on that field then it should do essentially the same thing.

Second, to order all other's based on your own Priority custom field. For this you simply need to do a second ordering in the JQL that also sorts on your custom Priority number field. The final query should end up looking something like this:

'project = NameOfProject and "My Priority" is not EMPTY ORDER BY "Status Marker" DESC, "My Priority" DESC'

 I also recommend making the your Priority field REQUIRED if it's not already. Otherwise you'll need to sort out the issues that have a null Priority.

Third, at the bottom of the reply I'll provide you with a pseudo code example of how I would calculate the "Calculated Due Date" field. You can use this pseudo code to finish out the field and then to make the "Actual Due Date" field as well.

Let me know if this helps and if you have any questions! :D

Aidan

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.search.SearchProvider
import com.atlassian.jira.jql.parser.JqlQueryParser
import com.atlassian.jira.web.bean.PagerFilter

def jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)
def searchProvider = ComponentAccessor.getComponent(SearchProvider)
def cfm = ComponentAccessor.customFieldManager
def user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()

// The search query
def query = jqlQueryParser.parseQuery('project = GibGob and "My Priority" is not EMPTY ORDER BY "Status Marker" DESC, "My Priority" DESC')
def results = searchProvider.search(query, user, PagerFilter.getUnlimitedFilter()).getIssues() as List

def issueIndex = results.indexOf(issue)
//--------Below is pseudo-code--------
//Check if issue is not the first issue in the query
if(issueIndex != 0)
{
def previousIssue = results[issueIndex - 1]

def complexity = cfm.getCustomFieldObjectByName("Complexity")
def complexValue = issue.getCustomFieldValue(complexity)
def prevIssueActualDD = cfm.getCustomFieldObjectByName("Actual Due Date")
def actualDDValue = previousIssue.getCustomFieldValue(prevIssueActualDD)

//Proper date math needs to be done here
return actualDDValue + complexValue + 3//days
}
else //If the issue is the first in the query
{
def today = null // This needs to be set
def complexity = cfm.getCustomFieldObjectByName("Complexity")
def complexValue = issue.getCustomFieldValue(complexity)

//Proper date math needs to be done here
return today + complexValue + 3//days
}

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Thursday in Jira Service Desk

How the Telegram Integration for Jira helps Sergey's team take their support efficiency to the bank

...+ reading Fantasy). The same is true for him at the bank he works for: Efficiency is key when time literally equals money. Read on to learn how Sergey makes most of the time he has by...

351 views 2 4
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you