Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
badges earned

Your Points Tracker
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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.

1 answer

0 votes

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.

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!

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

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.

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:

Wed 1-1.png

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.

Like # people like this

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".

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.

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

Suggest an answer

Log in or Sign up to answer
Site Admin
Community showcase
Published in Confluence

🥓🙅🏻‍♀️ Meet-less May Badge!

Hello Confluence Community!  What if i told you that you could have a healthier life and be 100% meet-less? This month, we're promoting a healthy, balanced work diet with Confluence. We la...

205 views 2 13
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you