Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,462,315
Community Members
 
Community Events
176
Community Groups

Need filter for tickets not updated for last 3 days excluding weekends

I am using "updatedDate <= -3d" option as of now, but i need to exclude weekends(sat,sun). Any ideas/suggestions on this would be helpful.

3 answers

3 votes
Phill Fox Community Leader Feb 02, 2022

To refine my answer for clarity and thanks to @Steph Sjostrom , @Reply Test Group for their inputs.  

 

The original answer was pulling updates in the last 3/5 working days.  I used this with a NOT criteria to pull the ones before. Which I admit I did not make clear in the original post. 

So you can do 

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

The following identifies issues updated before 3 working days without using NOT.  

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

Hi @Satish M 

Welcome to the Atlassian community where other users try to help each other. 

You need to split your problem in to two parts. 

The easy part as you have already is the current week within the last 3days

The second part is to look for when the 3 days straddles a weekend. In which case you want to make this 5days but you only want entries that are in last week and not in current week. 

so you can add the criteria
updated >-3d OR (updated > -5d AND updated > startofWeek(-3d) AND updated < startofweek(1d))

What this does is look for updated within the last 5 days that are updated since the start of the week beginning on a Thursday and before the start of the week beginning on a Sunday. The important part here is that the -5d will roll forward as the week progresses so on a Monday that will reference Thursday but on Tuesday it will reference Friday whilst the startOfWeek will remain locked on Thursday to Sunday. Once you get to Wednesday the -5d will be Saturday and so will exclude the previous week as you now have Mon-Wed in the current week. 

Hope this helps you

Phill

 

Feb 2022 - amended answer to be clear on startOfWeek being defaulting to Sunday.

Thanks Phill, tried that, but it didn't work, any other alternative option?

Just put:

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

, with the 'd' after the first '3'  ; )

 

Cheers

I tried this but it didn't work, any idea?

For me I found

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

was giving me tickets updated in the last 3 days. Changing the first part to

updated <-3d

worked for me.

Like Reply Test Group likes this

@Phill Fox how would i write this to account for issuetypes created JUST on the weekend, saturday and sunday?

Phill Fox Community Leader Feb 09, 2022

@BrentBot 

You need to think about whether you just want the last weekend or all weekends. The solution for last weekend is actually quite straight forward as Jira by default defines the startOfWeek as being Sunday

So you can use

created >startOfWeek(-1d) AND created <startOfWeek(1d)

What the -1d and 1d parameter does is move the date back and forward by one day. 
So the first condition looks for created since the start of Saturday and the second criteria makes sure the ticket was created before the start of Monday.

 

For all weekends I would look to use one of the script Apps to build a custom JQL extension. But others may have better ideas. 

 

Full disclosure I work for Adaptavist so ScriptRunner is my scripting tool of choice but other apps are also available. 

 

Phill

haha thank you for your insight and upfront abut those apps. do you have a link to the marketplace scriptrunner app - does that work on cloud or is that still just for on prem

@Phill Fox @Steph Sjostrom 

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.

@Phill FoxCould you help me here?

 

This is what I have:

project = "Delivery Management System" and (labels = DMSE or "Assigned Team" = DMSE) and status not in ("In Release", Released, Open, "Not Done", "Closed") and issuetype = Story AND Sprint in openSprints() AND NOT status changed AFTER -7d

 

What I need:

Tickets which are not in the mentioned statuses, with the last status update 7 days ago, excluding saturday and sunday.

 

Thank you,

Suggest an answer

Log in or Sign up to answer