Sort issues in a filter by due date (or today's date if due date blank)

[IT Infrastructure]

I want to sort issues in a filter by due date then "review date" (custom field), but where the due date and/or review date is blank, assume the current date and time.

Logic ideally is:

if duedate is blank then set field to current date and time otherwise if due date within four days of current date and time return due date - three days otherwise return due date

Is this possible? I've used calculated date fields but get issues with sort ordering.

[Fidel Castro Armario]

Please, confirm the version of JIRA and JIRA Workflow Toolbox you are using.

[Fidel Castro Armario]

I reproduced the problem, but simply by reindexing the JIRA instance the problem was fixed.

[IT Infrastructure]

Yes, that is my experience too - however, when I edit a field in the issue, the ordering is lost again. Not useful for triagle and prioritisation filtering...

[IT Infrastructure]

Is there a better way to do what I'm trying to do? The datePart and addDays functions feel clunky.

[Fidel Castro Armario]

Hi @veeve,

Yes, I think that you can simplify your expression. I can help you to do it, but first I need to understand why are you adding 1 day to Current date and time and to Due date, when supposedly, according to your description, you want to use Current date and time and Due date straight.

[IT Infrastructure]

I found one or both of the date functions rounded down to 12:00AM when I actually wanted to add X days to currentDateAndTime - e.g. it's 03/Aug/17 11:14 now - subtracting four working days should return 28/Jul/17 11:14

[Fidel Castro Armario]

Function datePart() is used to round a time instant to 00:00 (or 12:00 am) in a particular timezone. If you want to take into account the time part, you shouldn't use datePart() at all.

You can also use much more advanced time calculation function available since version 2.2.39: addTime(). This feature is based on SDG, and will allow you to implement custom schedules: support holidays, work times (e.g., from 8:00 to 15:00), and almost any requiement you may have in relation to work-schedules.

Using the following schedule definition MON-FRI { 00:00 - 00:00; } turns addTime() equivalent to addTimeSkippingWeekends(). Current version (2.2.40) doesn't support adding negative time, but you can use version 2.2.41_beta_2 which does it.

You can try the following expression:

{00012} = null ? nextTime({00057}, "my_schedule", LOCAL) : (timeDifference({00012}, {00057}, "my_schedule", LOCAL) < 4 * {DAY} ? addTime({00012}, - 3 * {DAY}, "my_schedule", LOCAL) : {00012})

Function nextTime() is used to show the closets future time that belongs to schedule in case current date and time is not in schedule (i.e., if it's weekend).

[IT Infrastructure]

The issue with this is, it calculates per-row rather than per-resultset - this means the milliseconds are different and so I can't sort by it. I think that's why I used datePart in the first place.

Please advise further?

[Fidel Castro Armario]

Hi @veeve,

Sorry I don't mind what do yo mean by "it calculates per-row rather than per-resultset".

I think that the problem is that JIRA uses indexes for ordering issues in issue listings, but indexes are static and an issue is only reindexed whenever any of its fields is edited, or the issue is transitioned. The problem is that calculated fields depend on many different variables, and they can't be practically indexed.

A solution would be that JIRA doesn't use indexes for calculated fields, but that seems to be not the case. I will investigate further on the subject, and eventually will open an issue to Atlassian about the matter.

[IT Infrastructure]

So, if there are two rows in the filter results, the calculation is applied as the data is retrieved - therefore assuming DueDate is blank, the two values for the calculated field could be 14/08/2017 17:00:00:01 and 14/08/2017 17:00:00:02, resulting in a sort on it always coming out in the order retrieved. If they came out as 17:00, this would solve the issue as the next sort field would then be used.

[Fidel Castro Armario]

Yes, you are right.

If you need time detail up to minutes, you can try this expression:

({00012} = null ? nextTime({00057}, "my_schedule", LOCAL) : (timeDifference({00012}, {00057}, "my_schedule", LOCAL) < 4 * {DAY} ? addTime({00012}, - 3 * {DAY}, "my_schedule", LOCAL) : {00012})) - modulus({00012} = null ? nextTime({00057}, "my_schedule", LOCAL) : (timeDifference({00012}, {00057}, "my_schedule", LOCAL) < 4 * {DAY} ? addTime({00012}, - 3 * {DAY}, "my_schedule", LOCAL) : {00012}), {MINUTE})

Using only the date part would be more efficent. To do it you should use the following expression:

datePart({00012} = null ? nextTime({00057}, "my_schedule", LOCAL) : (timeDifference({00012}, {00057}, "my_schedule", LOCAL) < 4 * {DAY} ? addTime({00012}, - 3 * {DAY}, "my_schedule", LOCAL) : {00012}), LOCAL)

[IT Infrastructure]

I couldn't get the second one working. I'm not sure the first one is working - it formats correctly but I feel the underlying data is still presented with small discrepancies. I've cut the formula back to

{00012} = null ? datePart( nextTime({00057}, "my_schedule", LOCAL), LOCAL ) :  datePart ( {00012}, LOCAL )

reindexed and will see how it works in practice. Will update with findings.

[IT Infrastructure]

I've added back in the timeDifference part and a due date of tomorrow (24th) returns 14th with this - assuming the -3 * {DAY} part is working on 24 hours rather than 8 and therefore taking off (8/)9 days rather than 3?

[Fidel Castro Armario]

Hi @veeve,

{DAY} always represents natural day duration. If you want to consider 8 h workdays, you should use - 3 * 8 * {HOUR} instead of - 3 * {DAY}.

[IT Infrastructure]

Yes, that is what I have done - I would have assumed the schedule should have got round that though? (Our schedule is MON-FRI 09:00 - 18:00)

[Fidel Castro Armario]

Schedules define a subset of time continuum, but time macros always represents natural time durations. If you use functions for schedules, the time calculations considering only the part of the time continuum defined by the schedule.

BTW, 9:00 to 18:00 is a duration of 9 h. So your workday is 9 h, not 8 h.