DueDate in Pie Chart

Question

Hi,

my client wants to have DueDates clustered and displayed within some sort of chart. Since there is noch general Bar-Chart (why the hell not?) I thought that the pie chart could do the trick. But no DueDate or any other date field can be selected. Am I doing something wrong?

Side note: we dont have the possibility to use addons.

Ste Wright · Answer

Hi @Marcel Rode

The Select List holds IDs, rather than dates.

The method below takes some setting up, but it's an alternative which requires no apps.

---

Setup:

* Create a custom Text Field (single line) field - eg. Due Date (Text)* Take note of the custom field ID, eg. 10123

* Create a custom Select List (single choice) field - eg. Due Date (Select List)* Make each select list option a date, using your preferred format - below I've used dd/MM/yyyy
* Take note of each option's ID - eg. 10001, 100002, 10003, etc

---

Rule Explained:

* The rule will first place the date into the text field, using the specified format
* Next, the rule uses a Lookup Table, which provides "keys" we can compare to the text field - and then dynamically set the select list value. This avoids the need for hundreds of IF/ELSE statements
* The IDs below are as follows...* The Lookup Table's values are each Option ID from the select list (matched to the option name, which should be the same date as the key column)
* The second Edit Issue uses the text field's Custom Field ID

---

Rule:

* Trigger: Field Value Changed* Field = Due Date
* Change Type = Value Added

* Action: Edit Issue* Field = Due Date (Text)* Value = {{issue.duedate.format("dd/MM/yyyy")}}

* Action: Re-fetch issue data
* Action: Create Lookup Table* Table Name = duedatetable* Table entries...* Row 1:* Key = 10/08/2023
* Value = 10000 - option ID

* Row 2:* Key = 11/08/2023
* Value = 10001

* Row 3:* Key = 12/08/2023
* Value = 10002

* Row 4:* Key = 13/08/2023
* Value = 10003

* Row 5:* Key = 14/08/2023
* Value = 10004

* ...etc

* Action: Edit Issue* Field = Due Date (Select List)* Value = {{duedatetable.get(issue.customfield_10123)}}

---

Notes:

This workaround has some limits...

* The Lookup Table has a limit of 20 entries (soon to be 200 - see this post), so is limited to 200 date options
* The above means the rule will need to be updated on a semi-regular basis, and assumes that due dates are within a 200 day radius

You could make the select list week-based rather than day-based? That should make it more viable given 200 weeks is ~3.8 years?

---

Let us know your thoughts!

Ste

Marcel Rode · Answer

Hey @Ste Wright

since we dont have the option to use Apps I've tried the first option but I could not make it work.

I've created a "Single List (single choice)" field and populated it onyl with an initial value of "31.12.9999".

After that I created a rule where when status is change it should change the value of this field to {{now}} assuming that it would add the value which it didn't. Could you tell me which option to choose in automation to add vaules to the list? I couldn't find something even on my test jira where I can use all options.

Ste Wright · Answer

Hi @Marcel Rode

I assume it's because date fields operate based on date/time (even the date pickers), so it'd be difficult to create a dashboard in this manner.

Your options would be to...

* Use Automation to populate a field you can filter by, such as a select list field, which lists all the date options
* Use an App which extends the power of dashboards/reporting. I've not tested all of these, but perhaps look at...* Custom Charts for Jira
* eazyBI
* Reports - Charts and Graphs
* ...or another App from the Marketplace

Ste