1. Fidel Castro Armario
  2. JIRA Workflow Toolbox
  3. Issues

Issues

Issue #712 new

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

Tech Department
created an issue

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.

Comments (13)

  1. Fidel Castro Armario repo owner

    Hi Tech Department,

    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.

  2. Tech Department reporter

    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

  3. Fidel Castro Armario repo owner

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

  4. Tech Department reporter

    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?

  5. Fidel Castro Armario repo owner

    Hi Tech Department,

    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.

  6. Tech Department reporter

    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.

  7. Fidel Castro Armario repo owner

    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)
    
  8. Tech Department reporter

    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.

  9. Log in to comment