Monthly Average Calculation

[Smita Kalunge]

Hi,

I am trying to get an average on a monthly basis.I have few issues from the month of June 2022 up to current month.

I have already calculated a number of days taken to complete the ticket. But now I'm looking for the month-by-month average.

(i.e. I need to sum off all no.of days taken to complete the ticket for all the issues that in June 2022. and then for July 2022,and furthermore...)

And after that, I need to get an average for a monthly basis.

so in short, I need to calculate -- the sum of all the total_days_taken/no. of tickets in specific months to get an average.

But how to calculate it in Eazy Bi.

Below is the table for reference.

		Issue Created Date	Issue Updated Date	Total_Days_taken(updated date-created date)
+June 2022	FCII-13493 Item	15-06-2022 04:51	21-06-2022 07:47	6.122222222
	FCII-13494 Item	16-06-2022 06:15	19-06-2022 03:15	2.875
	FCII-13486 Item	17-06-2022 05:20	20-06-2022 04:58	2.984722222
	FCII-13485 Item	17-06-2022 07:18	20-06-2022 02:50	2.813888889
	FCII-13483 Item	19-06-2022 06:30	20-06-2022 06:05	0.982638889
	FCII-13482Item	20-06-2022 05:28	23-06-2022 06:10	3.029166667
+July 2022	FCII-13481 Item	20-06-2022 04:51	24-06-2022 04:51	3.999999942
	FCII-13480 Item	20-06-2022 04:51	25-06-2022 04:32	4.986805556
	FCII-13479 Item	23-06-2022 04:51	26-06-2022 05:10	3.013194502
	FCII-13494 Item	24-06-2022 04:51	27-06-2022 04:51	3

Kindly help with this that would be appreciated.

Many Thanks,

Smita Kalunge

[Valeriia_Havrylenko_SaaSJet]

Hello @Smita Kalunge   👋 

If you are already allowing to install add-ons separately, as an alternative , I guess you can try Time in Status for Jira Cloud (developed by my SaaSJet team) with 7 types of status time reports so you will get your average monthly basis report easily.

For example The Average time report shows the average time issues have been staying in statuses during a specific period.  

Chart view:

Add-on has a 30-day free trial version and free up to 10 users. 
Please, let me know if you have any questions

Hope it helps 😌
Valeriia

[Mehmet A _Bloompeak_]

Hi @Smita Kalunge

As an alternative, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For further details, you can have a look at Status Time Reports How to Videos.

• This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

• You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
• Status durations are calculated according to the working calendar you define. Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days.
• You can set different duration formats.
• You can export reports in CSV file format and open them in MS Excel.
• You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
• You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

If you are looking for a completely free solution, you can try the limited version Status Time Free.

Hope it helps.

[Ilze Mezite]

Hi, @Smita_Kalunge

Thank you for your question. I answered you in our community already.

Here is the answer:

​
​Our default measures include Average calculations - like  "Average resolution days" and "Average closing days," and these measures use closing dates. Please read more about our default measures here: https://docs.eazybi.com/eazybi/data-import/data-from-jira/jira-core-measures-and-dimensions

If you want to calculate the duration between the creation date and the last updated date, that is not our default measure, but here is a formula example on how to calculate the Average over time for the duration between the issue creation date and the last updated date.
​
```
AVG(
Filter(
Descendants([Issue].CurrentMember, [Issue].[Issue]),
DateInPeriod(
[Issue].CurrentHierarchyMember.Get('Updated at'),
[Time].CurrentHierarchyMember)
),
CASE WHEN
-- filter currently resolved issues by any dimension, except transition status
[Measures].[Issues last updated] > 0
THEN
DateDiffDays(
[Issue].CurrentHierarchyMember.get('Created at'),
[Issue].CurrentHierarchyMember.get('Updated at')
)
END
)
```

Also, we have a DEMO account with reports already prepared for different business cases. Please look into our Age, Lead, and Cycle time reports here:
https://eazybi.com/accounts/1000/dashboards/7513-age-lead-and-cycle-time

Kindly,
Ilze
support@eazyBI.com

[Smita Kalunge]

Hi @ ilze.mezite

Thanks for the solution.
I tried out this solution but it’s giving me a blank column.
Also can we please calculate the month by month average.for June, July ,Aug, n more.
e.g. To calculate the average for June2022, we need to use the logic like (sum of the total_days_taken divide by total count of tickets or number of tickets) that happened in June month only.
Thanks !!

[Ilze Mezite]

Hi, @Smita Kalunge 

I'm sorry to hear that. This formula is basic - if there is an updated issue in the period, it should show in the report.

Did you perform the data import?

To help you more I need more information: report definition for example or print-screen of the report. 

Please contact support: support@eazybi.com

Kindly, 

Ilze

[Smita Kalunge]

Hi Ilze Mezite,

I didnt perform the data import.

And actually this is my office work, hence I am unable send the print screen because all this is I am asking from my personal laptop.

But the dashboard in eazyBi is looking same as I shared it above with few of the examples- and it's till date.

so till now I have been reached up to total_number _days_taken, but now looking for an average as I mentioned in my above question.

Thanks,

Smita

[Ilze Mezite]

Hi,

Please see the replay in our community post. I replied to you there.

Kindly,

Ilze

[Emre Toptancı _OBSS_]

Hello @Smita Kalunge

The data needed for such a measurement is present in each issue's history but Jira does not give this as a ready-to-use report. For that, you will need to use a marketplace app.

Our team at OBSS built Timepiece - Time in Status for Jira exactly for this. It is available for Jira Server, Cloud, and Data Center.  

Time in Status mainly allows you to see how much time each issue spent on each status or each assignee. 

   

You can combine the time for multiple statuses to get metrics like Issue Age, Cycle Time, Lead Time, Resolution Time etc. 

For all numeric report types, you can calculate averages and sums of those durations grouped by the issue fields you select. For example total in-progress time per customer or average resolution time per sprint, week, month, issuetype, request type, etc. The ability to group by parts of dates (year, month, week, day, hour) or sprints is particularly useful here since it allows you to compare different time periods or see the trend. 

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. It supports both Company Managed and Team Managed projects for Jira Cloud.

Timepiece - Time in Status for Jira reports can be accessed through its own reporting page, dashboard gadgets, and issue view screen tabs. All these options can provide both calculated data tables and charts.

  

EmreT

[Smita Kalunge]

Hi Emre Toptancı _OBSS_

Thanks a lot for the brief solution.Appreciate it.

As you suggested about the app, actually I couldnt find the app option in my Jira because I need it for my office work.So I think I cant use the feature you have mentioned here in solution.

Can we use any custom formula for it please? That would be helpful.

Many Thanks!!

[Emre Toptancı _OBSS_]

Hello @Smita Kalunge

You are correct. Our app, Timepiece - Time in Status for Jira, needs to be installed as a separate app to your Jira.

I am sorry since I am unable to provide help on easyBI functions.

EmreT

[Smita Kalunge]

No worries @Emre Toptancı _OBSS_, but thanks a lot for suggesting the same.

I will try to install it separately if my company is allowing it to do.

Thanks,

Smita

[Danut M _StonikByte_]

Hi @Smita Kalunge,

With our Great Gadgets app you can easily calculate this. One of the many gadgets that it offers is the Cycle Time Trend chart gadget that displays the average resolution time for past intervals, along with the average and trend. It is highly configurable.

  

All you have to do is to 1) create a filter with your issues 2) configure the gadget to use that filter and define as cycle to track, from the Issue Creation to the moment when the issue reached a "done"-category status. 

This way you will have the chart in real time. The chart also offers a data table with details about each issue! 

The app offers many other gadgets that you might find useful. To make a better idea, see also this article on our blog: An effective dashboard for Service Desk and Customer Support teams in Jira Service Management 

If you have any questions, please don't hesitate to contact support@stonikbyte.com.

Thank you,

Danut M.