Create a JQL filter that shows number of Jira issues closed today

[Mare Johnson]

Hello,

My name is Marè, and I am building a dashboard that shows an overview of overdue Jira issues for a daily report.

On top of "Two dimensional Filter Statistics" and "Filter Results" gadgets, I would like to show a total count of tickets closed today and that were still open yesterday.

The original JQL that gives me the list of overdue tickets is:

filter = XXXXXX AND "Target End" <= endOfDay() AND NOT (status in (Resolved, Closed) OR status = "In Review" AND labels = XXX_Ready_for_Test)

Yesterday, that JQL was showing 48 overdue tickets, and now the open tickets are down to 20. How can I show that 28 were closed between yesterday and today? Also, would it be possible to select a specific time of the day as well (let's say from 10AM yesterday until 10AM today)?

Thank you!

[Danut M _StonikByte_ · Accepted solution]

Hi @Mare Johnson,

For "tickets closed today and that were still open yesterday" try this:

filter = XXXXXX AND status IN (Closed, Resolved) and status WAS NOT IN (Closed, Resolved) ON startOfDay()

Danut. 

[Mare Johnson]

@Danut M _StonikByte_ thank you so much .. that worked!

[Bill Sheboy]

Hi @Mare Johnson 

The JQL functions you are using are all relative to the current date, and so your reporting will change every day.  Thus, you could check for work items resolved after the start of today and which were created before that timeframe:

filter = XXXXXX
 AND resolved >= startOfDay()
 AND created < startOfDay()

If instead you want more static measures (e.g., what was the count on last Wednesday, Thursday, etc.), please investigate marketplace or external apps to manage the data.

Kind regards,
Bill

[Mare Johnson]

 HI Bill,

Thank you for your suggestion, however the addition to my JQL shows no tickets whatsoever. 

[Bill Sheboy]

Thanks for that information, @Mare Johnson as it indicates your work items are not being marked with a resolution.  I am glad to see the other suggestion helped you!

[Rudy Holtkamp]

I think this should do it:

filter = XXXXXX AND "Target End" > startOfDay(-1) AND "Target End" <= endOfDay(-1) AND NOT (status in (Resolved, Closed) OR status = "In Review" AND labels = XXX_Ready_for_Test)

[Mare Johnson]

Hi Rudy, thank you for your reply.

Unfortunately that doesn't work .. I got now over 1000 tickets that are not closed.