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
-
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
-
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
-
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”:""}]
}