Create report if the custom date field has been empty for more than 7 days

[markus.neuwald@canon.at]

Hello!

We use Jira to assign installation tasks to technicians.

After assigning the task to the technician, the technician has 7 working days to contact the customer and organize an installation date.

There is a custom field configured in Jira called "Installation Date" [Date] where the technician enters the organized date.

Now I would like to create an annual report on how often the technician needs more than 7 working days.

Is this possible and can you please give me a tip on how to do this?

Kind regards,

Markus

[Bill Sheboy · Accepted solution]

Hi markus.neuwald@canon.at 

That information is in the issue history, but it cannot be easily queried for a custom field such as your date field.  There are probably marketplace addons which can do this type of reporting: https://marketplace.atlassian.com/

It might also be possible to create an automation rule to read the issue histories, and calculate the time between issue created and setting that field, and save that in a custom field.  There are some constraints on this approach, so if you need this type of report often consider checking the marketplace for options.

Kind regards,
Bill

[markus.neuwald@canon.at]

Hi Bill,

thank you for your reply.

Is there a marketplace addon that you would recommend for my request?

kind regards,

Markus

[Bill Sheboy]

I have not used any like that before so I do not have a recommendation. 

Perhaps search on "issue history" in the marketplace to find examples that may help, installing trial versions for comparisons.

[markus.neuwald@canon.at]

thank you.

[Aron Gombas _Midori_]

You can do this with the Better Excel Exporter app and a few clicks in Excel.

First, to have the source data, export all installation tasks to Excel, at least with the "Installation Date" field, the "Created" field and the repeated field change history. It will look like this:

Then, using the Excel filter narrow the spreadsheet to those rows where the "Installation Date" field was changed from a blank old value to a non-blank new value. It will show the event of setting up the date for each installation task.

Then:

1. Add new column "Time to organize", and use formula to calculate the difference between the "Created" column and the "Change Time" column.
2. Add another column "Within 7 days?", and use a formula which results TRUE if the previous column is less than 7 days.

That's it! With this, you have the precise time required and whether that was less than 7 days.

From this point, you can filter to show only those where the 7-days criterion wasn't met, create a pivot chart to calculate the percentage for each assignee (technician) and do other kind of data analysis easily!

(Pro tip: if you have to create this report frequently, consider creating an Excel report template  that will deliver the report fully ready-made with one click!)

[markus.neuwald@canon.at]

Hi Aron, thanks for your suggestion. It sounds great and i will give it a try.

kind regards,

Markus