JIRA queries to search for Issues in a given date range and produce charts.

[Victor H Gomez]

Need help with a JIRA query that when run produces the following: Not at programmer but any help is appreciated.

1. Total number of tickets in a given date and or range in In-Progress, New, Ready For Test and Ready for UAT status.
   1. e. Today is Friday 9/7/18, I want to be able to capture all of the tickets say from 9/3/18 through 9/7/18
   2. When I run the query I want to get those tickets that fit the criteria.
   3. Then be able to produce a chart like this :
2. A second query to produce the Total number of issues in Dev Queue in a given date that fit the following criteria:
   1. 0-3 days old
   2. 4-7 days old
   3. 8-14 days old
   4. 15 + days old
   5. Dev Queue definition = all tickets that are marked as In Progress, New, Ready for Test status
   6. Run the query and produce a chart like this:
3. Run a 3rd query , this time for those tickets in the User Queue bucket that are older that xyz number of days and per day and that fit the following criteria
   1. 0-3 days old
   2. 4-7 days old
   3. 8-14 days old
   4. 15 + days old
   5. User Queue definition = All tickets marked at Ready for UAT status
   6. Run this query and produce something like this

Current Process to get this data today:

1. Run a JIRA filter for the JIRA projects I need
2. Then Export the Results to Excel
3. Once in Excel, I add 2 additional columns called Age and Date
   1. Age = is the age of the ticket in days, using the DateDif formula
   2. Date = Today’s date, say 9/4/17 which is then compared with the Created column from the Export.
4. Now my Excel data is ready for Pivot tables or for sorting/filtering to get the data I need.
5. Very manual work as I am not a programmer. So Any help is appreciated.

Thanks,

Victor Gomez

[David Leal]

@Victor Gomes I would recommend you to use Google Spreadsheet for your reports. You can use the Adds-on: Jira Cloud for Sheets it is an official Atlassian product and it is free. So you can setup your queries to obtain the information you need, and then to create the corresponding reports.

[Victor H Gomez]

removed duplicate charts...

[Jack Brickey]

I would suggest you edit your original post and embed the above.

[Victor H Gomez]

Thanks Jack, I will.

vhg.

[Victor H Gomez]

Jack, that has been done now. thanks for the suggestion

vg

[Jack Brickey]

@Victor H Gomez, how much time have you spent looking at the canned gadgets that come with Jira and are placed on dashboards. If you haven't I would certainly start there as you may find they meet your needs. Popular ones include: two dimensional, pie, average age, resolution time, etc. The thing I don't really get from your question is what is driving the date ranges. If they are deterministic and repeat, e.g. 'previous weeks', 'this month', etc. Then you can certain create filters that will work for you. However, if you want a solution where you have a canned filter that offers some form of date variables you will need an addon for that.

here is an article that explains the gadgets FWIW.

[Victor H Gomez]

Jack,

Good afternoon. Actually, not being an expert, i have spent about 1-2 hours a day, every day. (as I built the charts), i have looked at them and used them (the ones i know of i.e.  Time Since Chart, Recently Created Chart, Created vs Result Chart, etc,), but it seems either they don't provide the answer i am looking for.  Attached is one of the Gadgets i have used, but it does not provide the answer i need..  

[Victor H Gomez]

Forgot to mention, what drives the dates is the JiRA issues export to Excel every day.

Example: on 9/3 i run my query, get my 100 tickets and i do my export to excel.

on 9./4 i do the same, now i get 50 more, for a total of 150.

on 9/5 i do the same, now i have 210 total.

on 9/6. i do the same, now i have 250 total

on 9.7 i do the same run, now i have 300 total.

1. Now, i have week worth of data, ( 5 days ). for each day (9/3, 9/4, etc until 9.7)
2. i then export my 9.3 file to excel, and i add the Date and Age column.
3. I then calculate the age of the tickets based on that.
4. I do the same for the 9/4 data, 9/5, 9/6/ and 9/7
5. then i create the graphs below.

I am sure there is an easier way, but this is how i know how for now..

Thanks,

Victor Gomez

[Jack Brickey]

I would recommend you look at an addon, e.g. EasyBI. The link I provided will show a few options that might work for you other than EBI too. Certainly the manual method isn't scalable. 

[Victor H Gomez]

Jack, absolutely.. thank you for the feedback,. will keep looking. thanks vhg.