Issues in a status for last 20 working days

[Monali Panchal]

I am trying to filter out issues that are in the "client review" status for past 20 working days (excluding Saturday and Sunday). 

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())

Thanks in advance.

[Hannes Obweger - JXL for Jira]

Hi @Monali Panchal

as Trudy mentioned, you'll need additional tooling to exclude non-working days. If you're open to solutions from the Atlassian Marketplace, there's a number of apps operating in this space.

E.g., your use case would be trivial to solve using the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren’t natively available, including the time since last status change, and many, many more.

Of course, you can freely configure your team's working days and hours.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting.

Any questions just let me know,

Best,

Hannes

[Mehmet A _Bloompeak_]

Hi @Monali Panchal ,

I tried different alternatives and finally came up with the JQL below. It worked on my test project. Could you please try and let me know if it works on your project?

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)
)

If you need more comprehensive reports on status time, you can try Status Time Reports app developed by our team. 

Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days. It has various other reports like assignee time, status entry dates, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month). And all these are available as gadgets on the dashboard too.

Here is the online demo link, you can see it in action and try without installing the app.

If you are looking for a free solution, you can try the limited version Status Time Free.

If you have any questions, feel free to schedule a demo with us. We will be happy to help.

[Gizem Gökçe _OBSS_]

Hello @Monali Panchal ,

Welcome to the community!

Unfortunately as @Trudy Claspill mentioned there is no JQL code available natively to exclude weekend days.

If you say that working days are not very important to me and just getting reports on a daily basis is enough for me, you can use the JQL query below.

project="dm" and type="dm" and status="Client review" and not status changed after -20d

However, you will need to use a third party application if using working days is a must.

I would like to introduce you to a very advanced tool with which you can easily filter out issues that are in the "client review" status for past 20 working days. Timepiece - Time in Status for Jira ,the oldest and leading "Time in Status" app in Atlassian Marketplace, which is developed by my team at OBSS,  it is available for both Jira Cloud, and Data Center. 

Timepiece mainly allows you to see how much time each issue spent on each status.

The application also offers custom calendar support. Calendars can be configured based on your working hours, excluding holidays and non-working hours (even lunch breaks) from the calculation. Timepiece reports can be generated based on your custom calendars. 

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well.

Timepiece reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts. And the app has a REST API so you can get the reports from Jira UI or via REST. Also you can export the reports in to various formats (xlsx, csv etc.) easily. 

Visit Timepiece - Time in Status for Jira to explore how our JIRA add-on can revolutionize your metrics measurement process. Enjoy a 30-day free trial to experience the full range of features.

If you wish, you can also schedule a live demo. We will provide a comprehensive overview of the application and address any inquiries you may have.

Hope it helps,

Gizem

[Amay Purohit_RVS]

Hi @Monali Panchal 

Welcome to the community !!

As suggested as an alternative, an app can help here. If you are open to try out one, do take a look at 

Time in Status Reports 

One of the many reports which the app provides is Time in Status in hours/days to determine , how much time the issue spent in each status in the complete lifecycle of the issue. 

The screenshot below shows how you can define your working calendar and exclude weekends. And find issues which are in a particular status for more than 20 days.

More Details here 

Disclaimer : I work for the vendor who built this app

[Trudy Claspill]

Hello @Monali Panchal 

Welcome to the Atlassian community.

There is no JQL code available natively to exclude weekend days when using criteria that relate to date reaches such as CHANGED DURING and CHANGED AFTER.

The CHANGED AFTER criteria is redundant and not needed. That criteria would be ensuring that the results exclude issues that had a status change after the start of the current week. That is already covered by the previous CHANGED DURING criteria that excluded issues that have had a status changed in the past 20 days.

There might be a third party app that extends JQL functionality to allow exclusion of weekend days in a date related criteria. I don't know for sure if there is such an app as I haven't reviewed all of them for this specific feature.

When you say you want to exclude weekend days so you mean you want to include issues that may have changed status on a weekend? 

[Monali Panchal]

Hi @Trudy Claspill

Thanks for the quick response.

to answer your question i.e. you want to exclude weekend days so you mean you want to include issues that may have changed status on a weekend? - we generally don't have this scenario.

Thanks 

[Trudy Claspill]

Within the "last 20 working days" you will have had 8 weekend days. Just change the criteria to be 

not status CHANGED DURING ("-28d", now())

Or use

not status CHANGED AFTER "-28d"