Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Exclude weekends in JQL Query

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.

5 answers

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.

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

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?

Hi @Owen S 

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

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!

Hi @Owen S 

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

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

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

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

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

@Christos Moysiadis did you find a solution?

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Jira Service Management

JSM June Challenge #2: Share how your business teams became ITSM rockstars

For JSM June Challenge #2, share how your non-technical teams like HR, legal, marketing, finance, and beyond started using Jira Service Management! Tell us: Did they ask to start using it or...

308 views 9 7
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you