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

[Bunny]

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!

[Aidan Derossett _Adaptavist_]

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
}

[Gaston Valente]

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.

[Sonya S_]

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.

[Gaston Valente]

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

[Sonya S_]

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

[Deleted user]

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,