How do I run a date query on a Date attribute field that is based on another date field?

Question

I want to know how many stories within a specific fixed version were moved to Done on or before their Due Date in Jira.  We have an attribute set up to capture that last date the story went to the Done status.  So basically I want a query that only shows stories that meet this criteria.

Last Done Date is less than or equal to the Due Date  (Last DoneDate <= dueDate)

Because Last Done date is an attribute I don't have have the option of comparing it to another field, just static values.  That is useless as the Due Date values are all different.

Stepan Kholodov _ALM Works_ · Answer

Hello Lisa,

The Latest Transition to Done is a Structure attribute that indeed only exists in the structure where it was added as a column. You can filter out the structure and show only issues that:
- have the transition date and
- this date is less or equal to the individual Due Date of issues
by doing the following:

1. Add the Formula column to the structure with this formula in it: if(transition and transition <= duedate, "1") 
You would need to map the transition variable to the Latest Transition to Done attribute on the list(for this click on the variable on the list below the formula and select the attribute on the menu).
This formula will identify each issue that matches the requirement. "1" is just the indicator that will be returned if the conditions are met; you can replace it with any other symbol or a green checkmark, for example, using Wiki Markup format of the column and the (/) notation.

2. The add the Attribute filter, select the Formula column as the Attribute and set the Operator to is not empty. As a result, only issues that meet the criteria(and their parent/ancestor issues) should remain in the structure.

I hope this helps. Please feel free to contact us directly at support.almworks.com if you need dour assistance with anything else.

Best regards,
Stepan Kholodov
ALM Works

Mohamed Benziane · Answer

Unfortunately, you can't compare two fields in jira. You can try an addon like Scriptrunner

https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_datecompare