Hi Guys! So, I'm working on a dashboard, and my goal is to get the average resolution time for a certain subset of work items. I'm able to get Work Item Key, Created Date and Resolved Date, and to get the resolution time of each work item, I'm using a formula column as "date difference (Created at, Resolution at, Hour)". With that, I'm getting the resolution time, in hours, for each work item. Now comes the part I'm struggling with: How do I get the average of this date difference, considering the number of work items being queried? For example: Work item KeyCreated atResolution at Datediff WI-1 2025-10-27 15:27:35.144 2025-10-27 17:36:05.628 2 WI-2 2025-10-27 15:29:26.282 2025-10-28 19:03:04.62127WI-32025-10-27 16:14:59.0432025-10-29 15:44:10.92647 I have 3 work items, and the resolution time for each of them is in the column Datediff. The total resolution time would be 76h so the average would be 25.33h. What I’m trying to figure out: How can I calculate the average of the Datediff column directly in the SQL / query editor? I can calculate the Datediff individually, but I’m struggling to compute the average across all returned rows within Atlassian Analytics. Any suggestions on how to structure the query or use a nested query/CTE to achieve this? Thanks in advance!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Average Resolution Time

Vando Goncalves _e-Core_

Hi Guys!

So, I'm working on a dashboard, and my goal is to get the average resolution time for a certain subset of work items.

I'm able to get Work Item Key, Created Date and Resolved Date, and to get the resolution time of each work item, I'm using a formula column as "date difference (Created at, Resolution at, Hour)".

With that, I'm getting the resolution time, in hours, for each work item.

Now comes the part I'm struggling with:

How do I get the average of this date difference, considering the number of work items being queried?

For example:

Work item Key	Created at	Resolution at	Datediff
WI-1	2025-10-27 15:27:35.144	2025-10-27 17:36:05.628	2
WI-2	2025-10-27 15:29:26.282	2025-10-28 19:03:04.621	27
WI-3	2025-10-27 16:14:59.043	2025-10-29 15:44:10.926	47

I have 3 work items, and the resolution time for each of them is in the column Datediff.

The total resolution time would be 76h so the average would be 25.33h.

What I’m trying to figure out:

How can I calculate the average of the Datediff column directly in the SQL / query editor?

I can calculate the Datediff individually, but I’m struggling to compute the average across all returned rows within Atlassian Analytics.

Any suggestions on how to structure the query or use a nested query/CTE to achieve this?

Thanks in advance!

Accepted answers

Jamie Giantonio

@Vando Goncalves _e-Core_ if you want to use a single value indicator chart, hide all the other fields and then use the GROUP AND AGGREGATE function and get the AVG of your datediff column

If you want an average at the bottom of a grid view leave the 4 columns and Edit the chart settings. On the General tab, check Show row aggregation for Numeric columns.

Then on the Formatting tab, and a custom format for that column, and for aggregation chose Average.

All comments

Jamie Giantonio

@Vando Goncalves _e-Core_ if you want to use a single value indicator chart, hide all the other fields and then use the GROUP AND AGGREGATE function and get the AVG of your datediff column

If you want an average at the bottom of a grid view leave the 4 columns and Edit the chart settings. On the General tab, check Show row aggregation for Numeric columns.

Then on the Formatting tab, and a custom format for that column, and for aggregation chose Average.

Vando Goncalves _e-Core_

Thanks you SO MUCH @Jamie Giantonio !

Birkan Yildiz _OBSS_

Hey @Vando Goncalves _e-Core_

Here is another angle you might find helpful. Calculating average Resolution Time in SQL means you are calculating based on a 24/7 clock. A standard Datediff includes nights and weekends, which skew your metrics.

If you are open to use a marketplace app, Timepiece - Time in Status for Jira is designed to handle these calculations automatically without any complex queries.

You can use its Duration Between Statuses report to instantly get your Resolution Time (and other agile metrics you desire). The system automatically calculates the Average (or Sum/Median) for you.

Besides, you can group your issues by Created Date, Priority, Issue Type, or any other field to see the averages for those specific subsets instantly.

And you do not need to wait to get data you want. It calculates this for all your retrospective data immediately And of course, you can even display this data directly on your Jira Dashboard using its gadgets.

To learn more about Timepiece, check it out on the Atlassian Marketplace.

Full disclosure, I'm on the team that makes Timepiece - Time in Status for Jira.