Exclude weekends in JQL Query

[Madhu Mullangi]

I want to find out the tickets which are not updated for last 3 days[ Excluding Weekends ].

Is it possible using Jira JQL filters?

Thanks in Advance.

[Phill Fox]

Hi folks 

You might like to look at my answer on https://community.atlassian.com/t5/Jira-questions/Need-filter-for-tickets-not-updated-for-last-3-days-excluding/qaq-p/1437566 which is looking at the rolling last 3 work days. For an explanation of the logic I used there. 

Building on the JQL provided by @Christos Moysiadis 

(updated < -3d AND updated > startofWeek(-1d)) OR (updated < -5d and updated  < startofweek(-1d))

Hope this is still useful to someone out there.

Phill

[Madhu Reddy]

Hi @Phill Fox my Scenario changed and Now I want to find the tickets which are not updated for the last 2 days and Exclude the weekends.

Could you please help me in this regard.

[Phill Fox]

Hi @Madhu Reddy 

I covered that use case in this answer https://community.atlassian.com/t5/Jira-questions/Need-filter-for-tickets-not-updated-for-last-3-days-excluding/qaq-p/1437566  although this was for 3 days not 2.  Please review that for the description of the logic but the based additional criteria for time is 

(updated <-2d AND > startofweek()) OR (updated < -4d AND updated > startofWeek(-2d) AND updated < startofweek())

Phill

[Owen Scott]

Hey @Phill Fox !

Sorry to make you the defacto "ignore weekends" expert, but you seem the most knowledgeable!

I am trying, using the Jira Automation feature, to flag tickets (flag = Impediment) that get stuck in "In Progress" or "Peer Review" for 3+ days.

I tried the following query:

sprint in openSprints() AND status in ("In Progress", "Peer Review") AND ((statusCategoryChangedDate < -3d AND statusCategoryChangedDate > startofWeek(-1d)) OR (statusCategoryChangedDate < -5d and statusCategoryChangedDate < startofweek(-1d))) AND Flagged is EMPTY ORDER BY Rank ASC

Which makes the following checks:

• ticket is in current sprint
• ticket is in progress or PR
• status hasn't changed in 3 days (ignoring weekends)
• ticket is not already flagged

The 3 days + ignore weekends rule is not being respected. A ticket that transitioned to "In Progress" the previous day was flagged by the automation the following morning.

Any idea where I might be going awry?

[Phill Fox]

Hi @Owen Scott 

The first thing I would do to troubleshoot this is to look closely at the ticket that was identified and work out the reason the logic thought it should be flagged. 

Looking at your logic you need to check which of these two conditions matched as true. I cannot see a reason why an issue transitioned the previous day would match either of these conditions. 

• statusCategoryChangedDate < -3d AND statusCategoryChangedDate > startofWeek(-1d)
• statusCategoryChangedDate < -5d and statusCategoryChangedDate < startofweek(-1d)

Is it possible that the statusCategoryChangedDate field did not get updated on the issue? And so it was picking up a previous entry?

I am not clear why you are looking at startOfWeek(-1d) in this logic I would have used startOfWeek() 

Another thing to check is where Jira considers the start of week as being! This is a very useful configuration item that allows organisations to adjust to whether they consider the start of the week as a Monday or Sunday.  You can quickly check if this is the case by looking at any date picker and checking to see if the calendar is displayed as Sunday/Monday for the start of each week. 

On Cloud you change the setting on <yourdomain>.atlassian.net/secure/admin/LookAndFeel

For on premise this needs to be set in the JVM as it defaults to your server locale.

To override, you need to set JVM parameters on Jira service to language and country that is set for Monday e.g. Germany would have this

-Duser.language=de

-Duser.country=DE

However if we look at the logic you have around StartOfWeek in the two scenarios of Sunday and Monday week starts we might be able to identify a change that would work for you. 

With the start of the week on a Monday and executed on Monday: StartOfWeek(-1) will look at the previous week

Whereas the start of the week on a Sunday and executed on Monday: StartOfWeek(-1) will look at the current week

Hope this helps you and others troubleshoot their scenarios. 

Phill

[Owen Scott]

Hey @Phill Fox,

Thank you so much for this great insight. This stuck out to me:

Is it possible that the statusCategoryChangedDate field did not get updated on the issue? And so it was picking up a previous entry?

Which prompted me to learn the nuance of statusCategoryChangedDate! I assumed this meant status, but two statuses can be in the same category e.g. transition from "In Progress" to "Peer Review", while different statuses, does not trigger a category change. So my confusion over the ignore weekend logic was misdirected! 

Is there a way to check if status has changed in the last 3 days (ignoring weekends) irrespective of category?

First thing that comes to mind is something like status changed BEFORE...

But want to get your initial thoughts on if this will work and how it can be applied to my use case.

P.S. it's looking like our startofWeek() is Sunday!

Thank you again!

[Phill Fox]

Hi @Owen Scott 

Glad to have been able to point you in the right direction. 

So your query about looking at status changes leads me to point you at the advanced JQL documentation at https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-fields-reference-939938743.html and https://confluence.atlassian.com/jirasoftwareserver/advanced-searching-operators-reference-939938745.html

For certain fields (and luckily Status is amongst them) you can interrogate the history of the field. 

But combining some of what you had (and bearing in mind your Sunday start of Week) you might like to try this

(status CHANGED DURING ("-3d", now()) AND status CHANGED AFTER startOfWeek())

OR

(status CHANGED DURING ("-5d", now()) AND status CHANGED BEFORE startOfWeek())

BTW I did a presentation at the Wolverhampton ACE on Advanced JQL examples if you would like a copy please send me a message pfox(at)adaptavist.com

Hope this helps.

Phill

[Owen Scott]

@Phill Fox

Brilliant! In my case, because I want to query for lagging issues where status hasn't changed, I tried:

(not status CHANGED DURING ("-3d", now()) AND not status CHANGED AFTER startOfWeek())

OR

(not status CHANGED DURING ("-5d", now()) AND not status CHANGED BEFORE startOfWeek())

This appears to be working, but I will monitor over the coming weeks.

Thank you for your continued support—I seriously appreciate your thoughtful responses to my questions and others'.

[Andrii Ekezli]

Hi there,

can you please review my jql and suggest if it doesn't work as expected

(due = 4d and due < endOfWeek() and due > startOfWeek("+1d")) or (due = 6d)

The goal is to find tickets 4 days from now excluding weekends. As I see it correctly, the first part of this request (before or) will be executed on Mon and Sun to return Fr and Fr (next week) respectively. And the second part (after or) will be executed in the rest of the days

Tue return Mon next week

Wed return Tue next week

Th return Wed next week

Fr return Th next week

Sat return Fr next week

[Adolfo Miguel Nunes]

@Christos Moysiadis did you find a solution?

[Luiz Cezer Marrone Filho]

I have the same issue as the others, any solution? Thank you guys!

[Christos Moysiadis]

@Madhu Mullangi many many sorrys. i missread this not. I ll try to find out the solution and repost it here.

[Madhu Mullangi]

@Christos Moysiadis Thanks for your Response.

I am totally confused with your Answer.

I think you are not clear with my question.I want tickets which are not updated for lats 3 days[Thursday,Friday,Monday]  excluding weekends.

Today is Tuesday and my result should be tickets not updated from last Thursday  Onwards i.e. [Monday, Friday, Thursday].

Thanks in Advance.

[Christos Moysiadis]

Hi @Madhu Mullangi ,

yes i am sure its possible.try to run this jql: 

• updated >= startOfWeek(2d) and updated <= endOfWeek(-1d) 
  
  • Notes: 2d means , that you increase the days by 2
  • -1d means , that you decrease the days by 1

Hope this info is helpful for you. maybe some customisations in the -/+ days will be needed ,because i dont know how your working calendar is.

Extra info:

• https://community.atlassian.com/t5/Jira-questions/How-do-i-set-the-first-day-of-week-so-that-startofweek-returns/qaq-p/112868
• https://community.atlassian.com/t5/Jira-questions/What-time-do-the-startOfWeek-and-endOfWeek-functions-start-and/qaq-p/12401

Best regards

CM

[Dipak Naokar]

@Christos Moysiadis 

@Phill Fox 

My problem is very similar, hence I do not wish to open a new topic of discussion:

Here is my description.

We have a service level agreement (SLA) with our customer facing team. It says once the bug is created, within 3 business days, the status should move to  at least "IN-DEV" (closed is not possible as the Queue of tickets is long).

So we have created a custom field called as "SLA breach". This field should have "YES"  value if the duration between "NEW" and "IN-DEV" is longer than 3 business days (but exclude weekends).

Example:

• If the new bug is created on Monday (then), it should go to "IN-DEV" before Wed (EoD), else on Thursday - it should be SLA breach.
• If the new bug is created on Tuesday (then), it should go to "IN-DEV" before Thu (EoD), else on Friday - it should be SLA breach.
• If the new bug is created on Wednesday (then), it should go to "IN-DEV" before Fri (EoD), else on Saturday - it should be SLA breach.
• If the new bug is created on Thursday  (then), it should go to "IN-DEV" before next Mon (EoD), else on Tuesday - it should be SLA breach.
• If the new bug is created on Friday  (then), it should go to "IN-DEV" before  next Tue (EoD), else on Wednesday - it should be SLA breach.
• If the new bug is created on Saturday  (then), it should go to "IN-DEV" before  next Wed (EoD), else on Thursday - it should be SLA breach.
• If the new bug is created on Sunday  (then), it should go to "IN-DEV" before  next Wed (EoD), else on Thursday - it should be SLA breach.

I know the Automation and I have created the cron jobs to run every night.

My problem is -3d is not excluding the weekend. (So A bug created on Friday, is flagged as SLA breach on Monday itself as Sat and Sun are counted by system)

Can you please suggest me a JQL filter to have the SLA breach for above conditions that will exclude weekends from the counting of "3 days"?

In other words I just want to count business days.