how to query Jira issues in the past months and obtain the month value using jql query

[CyberSage]

I need to find issues in "in progress" status for the last 6 month and get a count of them by month. 

how do I get that month when the issue was in this status from Jql query so I can group by the month?

the visual is same as the Jira dashboard gadget "average time in status". see attached image as example. 

[Bill Sheboy]

Hi @CyberSage 

Jira has their interpretation of a Cumulative Flow Diagram (CFD), and you could "mis-use" that report by only showing in-progress status values to see what you requested.  To do that, select your time interval and disable backlog and done status values.

Best regards,
Bill

[Nir Haimov]

Hi @CyberSage 

If you have scriptrunner, i would create a scripted field that will hold the number/name of the month (from a date field).

Once you have that, you can create pie chart or many others that will do the count for you

[CyberSage]

Haimov:

  thanks for the reply.

  if I understand correctly, the idea is to have a groovy script to calculate the month value from a date field and then store the calculated value in the Jira issue, correct?

groovy script is triggered by changes such as transitions. 'in progress' is a state however. the goal here is to track in which previous month the Jira issue remains in 'in progress' state.   the logic will be complex, as it re-implements the JQL history query operators. 

is there a more straight forward way to just get the month value when using the Jql operator (status was 'in progress' and before endofmonth(-2))? 

anyway straight foward to save the month value as June (2 month before) will solve the problem. 

[Nir Haimov]

Hi @CyberSage 

scripted field is not triggered by action or state, it runs every time an issue viewed.

Any way, it will not help you to solve what you want, there is not JQL that works the way you want.

you will have to type it on your like so: 
status was in ("In progress") DURING (startOfMonth(-2), endOfMonth(-2))