nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JQL query to identify all issues active on a given date

Rachel Gornall

I wish to see a count of issues that were active on the 1st of each month through last year.

Active, as in not in a 'resolved' or 'transferred/rejected' status at that date or after it.

I'm struggling to create a suitable JQL query. If someone could give me a hand I'd really appreciate it! Thanks

Accepted answers

Trudy P Claspill

Hello @Rachel Gornall

Can you provide some example data to illustrate would should and should not be included in the results?

If an issue was active on Jan. 1 and still met the criteria on Feb. 1 should it be counted in both months?

Does it matter when the issue was created?

Are Resolved and "Transferred/Rejected" actual Status values or Resolution values?

If an issue transitions to Resolved and "Transferred/Rejected" after the first of the month but then transitioned out of those statuses, should it be considered active?

John Funk

Hi Rachel,

Try this: project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release) on 2024-01-01

This will show the list of issues that were in one of those statuses on January 1.

All comments

Trudy P Claspill

Hello @Rachel Gornall

Can you provide some example data to illustrate would should and should not be included in the results?

If an issue was active on Jan. 1 and still met the criteria on Feb. 1 should it be counted in both months?

Does it matter when the issue was created?

Are Resolved and "Transferred/Rejected" actual Status values or Resolution values?

If an issue transitions to Resolved and "Transferred/Rejected" after the first of the month but then transitioned out of those statuses, should it be considered active?

Rachel Gornall

Hi @Trudy P Claspill , thank you for your help.

-If an issue was active on Jan. 1 and still met the criteria on Feb. 1 should it be counted in both months? Yes

-Does it matter when the issue was created? No

-Are Resolved and "Transferred/Rejected" actual Status values or Resolution values? Resolution values

-If an issue transitions to Resolved and "Transferred/Rejected" after the first of the month but then transitioned out of those statuses, should it be considered active? Yes

We use a project to track risks rather than to develop software. So our use case is slightly different to the norm. The data consists of Issues (risks) which follow this workflow:

So the aim is to identify any trend in active risks.

John Funk

Hi Rachel,

Try this: project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release) on 2024-01-01

This will show the list of issues that were in one of those statuses on January 1.

Trudy P Claspill

-Are Resolved and "Transferred/Rejected" actual Status values or Resolution values? Resolution values

As per your diagram those are Status.

Jira does not have a native reporting option that would enable you to run one query and get the information parsed out for each of the past months.

The suggestion made by @John Funk could be replicated for each month, updating the date given in the query.

If you wanted a more dynamic option to give you that information on a monthly basis for the past X months, then you could create an Automation rule that would run on a schedule.

In it you could run X number of Lookup Issues actions using the same JQL suggested by John, but instead of using a fixed date you could use the startOfMonth() function

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth()

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-1)

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-2)

...

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-X)

After each Lookup Issues action you could create a variable to hold the count of issues found.

The final action in the rule would be to send that information in an email.

Screenshot 2024-08-07 at 5.49.14 PM.png

Example of creating a variable and sending the email.

Screenshot 2024-08-07 at 5.50.25 PM.png

There may be a more elegant way to do this. Automation doesn't have an explicit function to create a loop to say "do these same actions X number of times". But in this other post there is some discussion of ways to accomplish that.

https://community.atlassian.com/t5/Automation-articles/Branching-over-smart-values-in-Jira-Automation/ba-p/1741935#M546

Trudy P Claspill

I just realized though that John's suggested JQL misses part of your requirement -

Active, as in not in a 'resolved' or 'transferred/rejected' status at that date or after it.

To adjust for that, use this query.

project = ABC and status was in (Investigation, "Development Required", "Awaiting GA Release") on startOfMonth(-X) and status not in (Resolved, "Transferred/Rejected")

If the issue is in one of those last Statuses now, then clearly it was moved to that status sometime after the first of the specified month.

Rachel Gornall

You took my question and gave me more than I'd asked for but exactly what I needed! Thank you I'm very grateful.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-cloud

jql