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

[Satish M]

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.

[Phill Fox · Accepted solution]

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)

[Muhammad Moazzam Hassan]

can you suggest the answer as per the status, that the issue is in the following status for the last 3 business days?

[Ambrosino Salvatore]

Hi @Phill Fox , can you suggest me the same jql but using  "status changed"?  status changed can accept just "after" and "before". Thanks

[Phill Fox]

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.

[Satish M]

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

[Reply Test Group]

Just put:

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

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

Cheers

[Rachel Miles]

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

[Steph Sjostrom]

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.

[BrentBot]

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

[Phill Fox]

@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

[BrentBot]

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

[Dipak Naokar]

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

[Aubin Coste]

Hello,

I have kind of a similar request from my client. We need to setup SLA but we only have JSW licence (if you have JSM it's pretty standard to define working days and SLA adapts to the configuration).

So to answer this need, I used a rich filter they have and created a smart filter that will raise an alerte if the SLA is breached defined by the priority in JQL. In DC it would be very easy to say something like "priority = blocked AND "assigned" <= startOfDay(-10d, "business") AND status not in (Annulé, Ouvert)" but on Cloud it seems that "business days" function are not implemented ...

I don't get why tho because we have to define our team's calendar in JSW project so if it's to not being able to use ? why ?

The answers above does not work for me and I guess not for you either since we don't want to get tickets updated the past 3 business days but we want to get ticket that are older than a specified number of business days ... For now I have not being able to tell my JQL that I want it to count as business days ... If you have found a solution let me know.

P.S: I have also the scriptrunnes enhanced search but it seems that even their, it is not possible to count as business days ...

[Rajesh Ramankutty]

hi i need to send mail for inactive issue for last 2 days after it got assigned how to achive this

[Andrei Ionut Sandu]

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