How to filter for last working day in Jira JQL

filter, jira, jql

Solution

We have a similar issue on Monday morning where we need to review issues created over the last friday and the weekend rather than just the past 24 hours. You can't test whether today is Monday, but you can infer it:

AND (
(created >= startOfDay("-3d") AND created < startOfDay("-2d") AND created >= startOfWeek("-2d") AND created < startOfWeek("-1d"))
OR
(created >= startOfDay("-2d") AND created < startOfDay("-1d") AND created >= startOfWeek("-1d") AND created < startOfWeek())
OR 
(created >= startOfDay("-1d") AND created < startOfDay() AND created >= startOfWeek() AND created < startOfWeek("+1d"))
OR
created >= -24.5h)

This basically includes things from Friday if Friday was 3 days ago, Saturday if Saturday was 2 days ago and Sunday was yesterday.

In your case you want to show where:

- duedate is today

- OR

- duedate is yesterday (startOfDay("-1d")...startOfDay()) AND duedate is between Mon-Thurs (startOfWeek("+1d") .. startOfWeek("+4d"))

- OR

- duedate is last Friday (startOfWeek("-2d")) AND duedate is 3 days ago/today is Monday (startOfDay("-3d")...startOfDay("-2d"))

Note: This assumes the default US locale where startOfWeek() is Sunday

duedate >= startOfDay() OR
(duedate >= startOfDay("-1d") AND duedate < startOfDay() AND duedate >= startOfWeek("+1d") AND duedate < startOfWeek("+4d")) OR
(duedate >= startOfWeek("-2d") AND duedate < startOfWeek("-1d") AND duedate >= startOfDay("-3d") AND duedate < startOfDay("-2d"))

Problem

We use Jira Agile with a "Daily Scrum" board which filters for issues due in the last day. This will show us the issues we should have fixed yesterday and the issues we will fix today. This works great, except for Mondays. On Monday we want to see the issues that had a due date of friday or duedate of today. How can I achieve this using JQL? It seems JQL doesn't support `IF()`, correct? If it does, we might find a way using a compare like `now() == startOfWeek()`.

Original source