Exclude weekends in JQL Query

Madhu Mullangi March 12, 2019

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.

6 answers

5 votes
Phill Fox
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 21, 2020

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 July 27, 2020

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 27, 2020

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

Like Kent Simpson likes this
Owen Scott May 21, 2021

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 24, 2021

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 May 24, 2021

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
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 24, 2021

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

Like # people like this
Owen Scott May 24, 2021

@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'.

Like Conrad Wiebe likes this
Andrii Ekezli
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
January 31, 2022

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

Like Alexandra Lagnel likes this
Monali Panchal May 22, 2024

Hi @Phill Fox ,@Owen Scott 

can you please review my jql? It doesn't work as expected.

project="dm" and type="dm" and status="Client review" and (not status CHANGED DURING ("-20d", now()) AND not status CHANGED AFTER startOfweek())

I am trying to filter out issues that are in the "client review" status for past 20 working  days (excluding Saturday and Sunday). 

Like Bloompeak Support likes this
Bloompeak Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 4, 2024

Hi @Monali Panchal ,

You can use the below JQL to exclude weekends in the past 20 working days.

project="dm" and type="dm" and status="Client review" AND 
(
(status changed before startOfWeek(-22d) AND not status changed after -28d)
OR
(status changed after startOfWeek(-22d) AND not status changed after -26d)
0 votes
Rajesh Ramankutty May 19, 2024

Hi team,

 

I need to check only the tickets assigned and not taken any action for last 3 days of working days

0 votes
Adolfo Miguel Nunes
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 13, 2019

@Christos Moysiadis did you find a solution?

Luiz Cezer Marrone Filho
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
June 18, 2019

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

0 votes
Christos Moysiadis
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 12, 2019

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

0 votes
Madhu Mullangi March 12, 2019

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

0 votes
Christos Moysiadis
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
March 12, 2019

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:

Best regards

CM

Dipak Naokar June 2, 2022

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

Suggest an answer

Log in or Sign up to answer