Adding a column in Jira Filter to show if an issue is Past/Near/Far from/Missing a due date

[Johnny Chiang]

I have a filter showing all issues in a project. I would like to add a column to show if an issue is

1. past due: due date > today

2. near due: due date within 2 weeks from today

3. far from due: due date beyond 2 weeks from today

4. missing a due date: due date is not filled in

could you please advise how I can show that new column, say the column name as "due date status". Thank you very much,

[Walter Buggenhout]

Hi @Johnny Chiang and welcome to the Community!

A more common approach to the underlying problem you are trying to solve, would be to add separate filters for the different scenarios on a dashboard or add quick filters to your actual scrum/kanban board to assist you in context of your work, rather than setting up calculated fields for this.

These filters would be something like this:

Past Due

due < now() and Resolution = Unresolved

Near Due

due < 14d and Resolution = Unresolved

Far from Due

due >= 14d and Resolution = Unresolved

Due date missing

due is empty and Resolution = Unresolved

If you add 4 Filter Results gadgets to a dashboard, each based on one of the above filters, you will be available to see this information at any time.

Adding them as quick filters to a scrum or kanban board allows you to just filter out the issues matching the desired set of issues.

Hope this helps!

[Johnny Chiang]

@Walter Buggenhout thanks but is it possible to use only ONE Gadget to show all 4 different filters?

I'm actually trying to use two-dimension to show different assignees and their issues' due-status (past/near/far from/missing) in one table. This way I can manage the review meeting more efficiently and the audience can focus on only ONE Gadget table. Thanks again

[Hannes Obweger - JXL for Jira]

Hi @Johnny Chiang

if you're open to solutions from the Atlassian Marketplace, you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of advanced features, including support for  conditional formatting. With conditional formatting, you can define conditions on your issues - such as different due date ranges - and if these conditions are fulfilled, set the color of either the entire issue row or a particular cell. This is how it looks in action:

This is really just one of a virtually endless number of possible views and reports; you have full control over which data you want to show and highlight. I should also add that conditional formatting plays nicely in combination with JXL's many other advanced features, such as e.g. support for configurable issue hierarchies, issue grouping by any issue field(s), or sum-ups.

Any questions just let me know,

Best,

Hannes

[Valerie Knapp]

Hi @Johnny Chiang , welcome to the Atlassian Community and thanks for your question. 

In order to be able to add these columns to a JQL filter search, you will need to create custom fields and calculate these values as they don't exist in Jira. You can use automation to do this, for example, to check each day which issues are approaching their due date and update a field in the issue. Here is an example automation rule which is sending a notification when the issue is approaching its due date -https://www.atlassian.com/software/jira/automation-template-library/rules#/rule/9918938/207129065 

You would need to work out at which intervals you want to calculate these values and then create automations in order to have values in the columns / in the issues. 

I hope this helps but if you have more questions, please write us here and someone will try to help. 

Cheers

[Johnny Chiang]

@VAL thanks for your suggestion but I'm not familiar with JIra Automation so would you please advise how I can initiate an automation rule? thanks,

[Valerie Knapp]

Hi @Johnny Chiang , thanks for your feedback. 

You would need a scheduled rule that runs each day and updates a custom field you would need to create, such as Due Date Status. 

Then, based on a series of if conditions, you would update the Due Date Status for today for each issue. 

Here is an example rule which you could modify for your use case https://www.atlassian.com/software/jira/automation-template-library/rules#/rule/9918938/207129065 

Cheers