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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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'.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi team,
I need to check only the tickets assigned and not taken any action for last 3 days of working days
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Rajesh Ramankutty ,
Hope below JQL works.
assignee IS NOT EMPTY AND
NOT ( updated >-3d OR (updated > -5d AND updated > startofWeek(-3d) AND updated < startofweek()))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Christos Moysiadis did you find a solution?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have the same issue as the others, any solution? Thank you guys!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Madhu Mullangi many many sorrys. i missread this not. I ll try to find out the solution and repost it here.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi @Madhu Mullangi ,
yes i am sure its possible.try to run this jql:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.