How to calculate the issue average days to resolution, or average "lifetime" of issues

Dear There

I need re-write this topic… the key is now, how to calculate days for issues from created/submitted, to resolution, as well as the average “lifetime” of issues, per month. Please find brief report definition export below.

Now the issue numbers should be correct; however the average days are not sure… we actually need 3 kinds of days

  1. for issues resolved up to latest date in the month (if today is 4/26, then it calculate up to 3/31 for Mar, and up to 4/26 for Apr), how many days for all resolved issues from submitted to resolved, and the average number

  2. for issues unresolved up to latest date in the month (same as above), how many days for them from submitted to latest date in the month, and the average number

  3. total issue lifetime should be (1) + (2) on average again — so for each month, the total days for all issues up to date (unresolved) or up to resolution date (resolved), and the average number

{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “ThinOS SUS Monthly Metric #2 monthly average days (under development)”,
“folder_name”: “Leon Kan Li (please do not edit)”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issues created]","[Measures].[Issues submitted]","[Measures].[Issues resolved]","[Measures].[Total created unresolved Leon]","[Measures].[Total submitted unresolved Leon]","[Measures].[Average days from Open to Submitted]","[Measures].[Average days from Submitted to Resolved]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Month].Members"],“members”:[],“bookmarked_members”:[]}]},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].[All Projects]"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“ThinOS Defect Management”,“full_name”:"[Project].[ThinOS Defect Management]",“drillable”:true,“key”:“THINOS”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:[],“current_page_members”:["[Project].[ThinOS Defect Management]"]},{“name”:“Found By”,“selected_set”:["[Found By].[All Found Bies]"],“members”:[{“depth”:0,“name”:“All Found Bies”,“full_name”:"[Found By].[All Found Bies]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“Customer”,“full_name”:"[Found By].[Customer]",“parent_full_name”:"[Found By].[All Found Bies]"}],“bookmarked_members”:[],“current_page_members”:["[Found By].[Customer]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{“freeze_header”:false}}}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Transition to status first date”,“format_string”:“yyyy-mm-dd hh:nn:ss”,“formula”:“TimestampToDate(\n [Measures].[Transition to status first timestamp]\n)”,“annotations”:{“group”:“Default”}},{“dimension”:“Measures”,“name”:“Issue resolution date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Resolved at’)",“annotations”:{“group”:“Issue properties”}},{“name”:“Issues submitted”,“dimension”:“Measures”,“formula”:"([Measures].[Transitions to status issues count],\n[Transition Status].[Submitted]\n \n)",“format_string”:""},{“name”:“Average days from Open to Submitted”,“dimension”:“Measures”,“formula”:"(\n[Measures].[Days in transition status],\n[Transition Status].[Open]\n)\n/\n(\n[Measures].[Transitions from status issues count],\n[Transition Status].[Open]\n)\n",“format_string”:""},{“name”:“Average days from Submitted to Resolved”,“dimension”:“Measures”,“formula”:"[Measures].[Days submitted to resolved Leon]\n/\n[Measures].[Issues resolved]",“format_string”:"#,##0.00"},{“name”:“Total created unresolved Leon”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ \nAND [Measures].[Issues created]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues created]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues created]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Created at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues created]),\n 0\n ))\nEND”,“format_string”:""},{“name”:“Key Account”,“dimension”:“Customer Name”,“formula”:“Aggregate(\n { \n [Customer Name].[STANDARD CHARTERED BANK],\n [Customer Name].[MD ANDERSON CANCER CENTER],\n [Customer Name].[Deutsche Bank],\n [Customer Name].[Credit Suisse Poland],\n [Customer Name].[HCA healthcare]\n \n }\n)”,“format_string”:""},{“name”:“issue submitted date Leon”,“dimension”:“Measures”,“formula”:"([Measures].[Transition to status first date],\n[Transition Status].[Submitted],\n[Time].CurrentHierarchy.DefaultMember)\n",“format_string”:""},{“name”:“Days submitted to resolved Leon”,“dimension”:“Measures”,“formula”:“Sum(\n Filter(\n\t Descendants([Issue].CurrentMember, [Issue].[Issue]),\n([Customer Name].[Key Account]>0 AND\n[Measures].[Issues resolved]>0))\n ,\nDateDiffDays([Measures].[issue submitted date Leon], \n [Measures].[Issue resolution date])\n)\n”,“format_string”:"#,##0.00"},{“name”:“Total submitted unresolved Leon”,“dimension”:“Measures”,“formula”:“CASE WHEN [Issue].CurrentMember.Level.Name <> ‘Issue’ \nAND [Measures].[Issues submitted]>0\n\nTHEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Issues submitted]\n - [Measures].[Issues resolved])\n ))\n + [Measures].[Issues submitted]\n - [Measures].[Issues resolved]\n )\nWHEN [Time].CurrentHierarchyMember IS [Time].CurrentHierarchy.DefaultMember\nTHEN NonZero([Measures].[Issues due])\nELSE\n – optimized formula for drill through Issue\n NonZero(IIF(\n DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Submitted at’),\n [Time].CurrentHierarchyMember) AND\n NOT DateBeforePeriodEnd(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember),\n ([Time].CurrentHierarchy.DefaultMember,\n [Measures].[Issues submitted]),\n 0\n ))\nEND”,“format_string”:""}]
}

Please check out this demo report in our account:
https://eazybi.com/accounts/1000/cubes/Issues/reports/50084-average-age-till-resolution-report

Average days till resolution or period end will show the age of unresolved issues from issue creation date till each period end or till resolution.

You can use measure representing submitted based on the first date when issue moved to Submitted status instead of issue created date in the formula:

Issue submitted date:

([Measures].[Transition to status first date],
[Transition Status].[Submitted],
[Time].CurrentHierarchy.DefaultMember)

However, it will slow down the formula a lot. If you are using submitted date in many complex formulas, I would suggest precalculating the submitted date with some Scripted field in Jira or with some JavaScript calculated custom fields during import. Then you can use imported issue date property representing a submitted date in any calculation.

Daina / support@eazybi.com

In the case of wanting to do the calculation for the “children” of an epic, how should I present the code?