Milestones missed by x days

[Sharmila Hiranandani]

In my project I have a date field  called "planned end date". I want to filter issues which are not completed and have missed the planned end date by 5 days or more . How to get this list of issues  using JQL please 

Thanks

[David Nickell · Accepted solution]

resolution is empty and "planned end date" <= -5d

[Benjamin Lentz]

Hey Sharmila,

the correct JQL shoud be

project=XXX and "planned end date"<=-5d and statuscategory not in(Done) order by "planned end date" ASC

If you need to have it based on hours, you could also exchange -5d with -120h or other values.

You can change "order by" as you wish, this sorting would show the most missed days since milestone first in the list.

Best regards,

Benjamin

[Sharmila Hiranandani]

Thanks , this helps

[Stephen_Lugton]

@Sharmila Hiranandani if this helped you solve your issue, please consider clicking on the 'Accept Answer' button to help other people looking for a similar answer

[Dick]

Hi @Sharmila Hiranandani 

Basic arithmetic with dates is described in automation/docs/jira-smart-values-date-and-time/ 

kind regards,

Dick

[Sharmila Hiranandani]

Thanks. But I need to do this in JIRA issue filter . And the info mentioned in the document you shared does not work in JIRA issue filter / JQL. I am creating a status report in Confluence and I want to show the count of issues which have missed the milestone date by more than 5 days. 

Thanks

[Dick]

Hi @Sharmila Hiranandani 
The {{smart values}} give you an opportunity to construct the result into an output string

Also {{now.diff(issue.created).weeks}} is a nice example where the output is the number of weeks ago that an issue is created. That's only a hair apart from your request.

Combined with conditional logic described by Atlassian, it provides you with a means to create the content of the status report you desire. 

Kind regards,

Dick