cancel
Showing results for
Search instead for
Did you mean:
See all
See all
##### Groups
Explore all groups

# 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 votes
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 -->

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.ComponentAccessorimport com.atlassian.jira.issue.search.SearchProviderimport com.atlassian.jira.jql.parser.JqlQueryParserimport com.atlassian.jira.web.bean.PagerFilterdef jqlQueryParser = ComponentAccessor.getComponent(JqlQueryParser)def searchProvider = ComponentAccessor.getComponent(SearchProvider)def cfm = ComponentAccessor.customFieldManagerdef user = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser()// The search querydef 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 Listdef issueIndex = results.indexOf(issue)//--------Below is pseudo-code--------//Check if issue is not the first issue in the queryif(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
Community showcase
Published Jan 08, 2019 in Jira

### How to Jira for designers

I’m a designer on the Jira team. For a long time, I’ve fielded questions from other designers about how they should be using Jira Software with their design team. I’ve also heard feedback from other ...

1,098 views 4 9

### Atlassian User Groups

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

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.

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