Can I order by date values in a custom field?

Question

I have a Jira Software Project that tracks many projects that are expected to last a few months each and include a few milestone dates.  The eventual due dates can move and we've estimated numbers of weeks (lead times) to get through the stages of the individual assignments.  We have calculated fields that return the dates for these milestones based on the due date and the lead times.  The milestones are named "Expected Concept Start" and "Expected PO Date."

I'd really like to be able to use these fields in filters.  Two functions I want in particular are to be able to order by either of the two calculated dates, and to be able to color code issues in my Kanban view that are running late in the sense that their current status is earlier in the workflow than the date when they should have transitioned.

My problem is that I can't figure out how to filter on those custom fields.  If I just click the column header in a filter that shows issues with those fields, the order does change but it doesn't end up in order.  An example of that query would be

ORDER BY cf[29231] ASC

I can select the filter in the basic field, but if I put some criteria in the basic toolbar that I know an issue meets - for example, expected PO date between 12th and 14th December when I'm looking right at one that's supposed to start on the 13th - I don't get any issues back - just "No issues were found to match your search."

I've found some chatter that Quick Filters don't support custom fields.  Is there another kind of filter?

I've also seen some chatter about different types of custom fields having different results in terms of being able to be filtered on.

Andrew Wright_3ie1i8 · Answer

Hi guys,

Thanks for the suggestions.  I've asked a couple people who have more visibility into the back end of the project, but I'm just a project admin and can't see how things are implemented.  Sounds like I can't fix it with my level of access, anyway.

Max Foerster - K15t · Answer

Hi @Andrew Wright ,

what kind of calculated fields are you using? Because it could be that the fields are only calculated at runtime (when viewing an issue) and are not kept in the index, therefore you cannot sort by them as they have no value at that time. It's a guess. :)

Best, Max

Andrew Morin_cpsg1l · Answer

I think you will have issues using the calculated field as those filed types don't store a value in the custom field value table. The calculated fields render the results when you view the issue or you will see the value in the issue navigator.

I would try possibly using a script to calculate your date and have the script populate actual date fields. I think that would solve your sort issue.