What filter to show issues on a given date grouped by status?

[phil_brown]

There seems to be no way to simply specify a date. What I want is something like
project = XYZ AND status not in (backlog, cancelled) AND date = 2021/04/14 GROUP BY status.
I can only find createdDate or updatedDate but those obviously can't help.

[Nic Brough -Adaptavist-]

JQL is for finding issues, not reporting on them, it's not SQL.

For your "group by", you need to be looking at the reporting method, not the question of what issues to fetch.

Jira has a very simple gadget that does this, which you can import into Confluence. 

Run a search for the issues you want (project = XYZ and status not in (backlog, cancelled) should work fine, but "date =" makes no sense.  What date?), then use the saved search in a "filter statistics"gadget that has "status" as the field to group by.

[phil_brown]

Well, what I actually want is to find a set of issues that are in a given set of statuses (ie. not in the backlog, and not resolved) on a given day. It does seem that such an apparently simple thing is indeed a bit beyond the pale for JQL, which is why people use things like scriptRunner. I would suggest that "date" makes perfect sense, given I could have "updatedDate" or "createdDate" ... and I've seen some examples with the "on" predicate or "endOfDay" function which are close to what I want. And I don't want to use gadgets - due to JIRA's very limited reporting capabilities, I do in fact want to export the data to a spreadsheet :)  ... for reporting!

[phil_brown]

and yes, the group by I can do in the spreadsheet ... it's really the date function I need.

[Nic Brough -Adaptavist-]

No problem finding "issues in status X", but "date" is still not defined. 

Created, updated, commented, goes through a specific transition, and, and, and

JQL has some useful functions around dates, but they can't work with "on a given date", they need to know what date you are looking at.

[Katerina Kovriga _Stiltsoft_]

Hi @phil_brown ,

Please define the "date function". If the problem is that you need to work with different kinds of dates (created, updated, etc.) but you are tired of going to the page edit mode and changing the JQL inside your Jira Issues macro, we can suggest using additional apps from the Marketplace.

For example, here is a screenshot of our Table Filter and Charts for Confluence app (its Table Filter macro) in combination with the Jira Issues macro:

Here we filter issues only with the "In Progress" status and created later than 01/Jan/20.

The screenshot was taken in the page view mode, so you don't need to change any JQLs and can filter you issues (create pivots) on the fly.

[phil_brown]

Thanks @Nic Brough -Adaptavist- and @Katerina Kovriga _Stiltsoft_ - we're still far from answering this. Perhaps I will state a simpler, clearer case - basically the ask is this:

1. a JQL query that filters to provide a set of issues in a set of statuses on a given day
2. no plug-ins or JIRA specific gadgets or displays - the objective is to just give a list of the with their statuses, exportable as a CSV
3. again, this is NOT for any kind of status change (created/updated) - the date operator

I get that Atlassian's business model effectively forces you to buy add-ons to cover functionality that the product does not provide OOTB. But any answer that does not meet all 3 of the above criteria does not answer the question. It does seem to be that the answer is "this is not possible OOTB".

[Katerina Kovriga _Stiltsoft_]

As I understand, you want to see the historical changes of your issues. Like, for example, two days ago you had 5 issues to do/0 done, yesterday 3/2, today 2/3.

If to talk about Confluence, the Jira Issues macro and the Jira Chart macro reflect the current state of your issues without any historical memory.

[Nic Brough -Adaptavist-]

Ok, now you've told us what the date actually is, we can give you the clauses you need to add for them.  Try adding stuff like:

status WAS "My Status" DURING ("2020/01/17","2020/01/31")