How can I create an average time to close report in Confluence each month?

Question

Every month I run 2 filters. One is for all cases created in that month.

The second is for all cases marked as resolved or closed for that calendar month. I need some type of report that will show me the average time it took all of the cases to go from being created to closed or resolved in a given month.

Katerina Kovriga {Stiltsoft} · Answer

Hi there,

As this question mentions our Table Filter and Charts for Confluence app, we are happy to introduce its new macro – Table Spreadsheet.

This macro allows you to work with fully functional Excel spreadsheets right in Confluence. You’ll be able to use cells’ formulas, filters, conditional formatting, etc., create pivot tables and charts from the page view and edit mode.

The Table Spreadsheet macro is available for Cloud and Server/Data Center.

Natalie Paramonova _Stiltsoft_ · Answer

Hi Dave,

You can try to use the Table Transformer and Table Filter macros bundled in the Table Filter and Charts app.

Table Transformer let you use formulas to output a new column with the number of days spent on resolving a ticket with the help of the SQL query. Table Filter let you calculate the average number of days by outputting the total row.

Here is the example of using these macros:

1. Wrap the Jira Issues macro in the Table Transformer macro.

2. Select the SQL query tab and use the following query:

SELECT *, (('Resolved' - 'Created') / "24h") AS 'Days to close' FROM T*

3. Wrap the Table Transformer macro in the Table Filter macro, select the Table view tab and set the Count totals for columns option.

4. Save the macro and the page.

Damien Tan · Answer

it sounds like you have 2 JIRA filter nicely done to display only the issue tickets that was created and another for resolved on the given month.

if you are looking for a way in Confluence to perform calculation, i'm afraid there isn't one naively as of now.

Perhaps you can look into JIRA's third-party plugin to get the calculation and then report it in Confluence instead.