Dear Team,
-
in our project Status Category “Resolved” has Resolved, Integrated, Closed, Following Status. Once the ticket is resolved, then it gets integrated.
I have to calculate the average days of tickets with “Integrated” status. I tried to update and reuse some existing measures but I do not get consistent values. -
I measured Integrated & Transition_Int → I get different values, any clue why? I would expect the lower value should not contain unique count of issues which do not exist in the higher value.
Could you please advise?
Definition of my chart.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Inflow_Outflow_Average_BAT",
"result_view": "bar_chart",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues created]","[Measures].[Open issues]","[Measures].[To Be Integrated]","[Measures].[Integrated]","[Measures].[Avg_Age_days_Integrated]","[Measures].[Transition_Int]","[Measures].[Avg_Age_Integrated]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Last 12 weeks]"],"members":[{"depth":0,"name":"Last 12 weeks","full_name":"[Time.Weekly].[Last 12 weeks]","annotations":{"group":"Default","predefined":"true"},"dimension_hierarchy":"Weekly","calculated":true,"drillable":true,"expanded":true,"drilled_into":false,"removed":true}],"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":"IDCevo Development","full_name":"[Project].[IDCevo Development]","drillable":true,"key":"IDCEVODEV","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":[],"current_page_members":["[Project].[IDCevo Development]"]},{"name":"Issue Type","selected_set":["[Issue Type].[All Issue Types]"],"members":[{"depth":0,"name":"All Issue Types","full_name":"[Issue Type].[All Issue Types]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"TAEE Defect","full_name":"[Issue Type].[TAEE Defect]","parent_full_name":"[Issue Type].[All Issue Types]"}],"bookmarked_members":[],"current_page_members":["[Issue Type].[TAEE Defect]"]},{"name":"Domain","selected_set":["[Domain].[All Domains]"],"members":[{"depth":0,"name":"All Domains","full_name":"[Domain].[All Domains]","drillable":true,"type":"all","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Domain].[All Domains]"]},{"name":"Label","selected_set":["[Label].[All Labels]"],"members":[{"depth":0,"name":"All Labels","full_name":"[Label].[All Labels]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"BAT_blocker","full_name":"[Label].[BAT_blocker]","parent_full_name":"[Label].[All Labels]"}],"bookmarked_members":[],"current_page_members":["[Label].[BAT_blocker]"]},{"name":"Assigned ECU","selected_set":["[Assigned ECU].[IDCEVO-25]"],"members":[{"depth":1,"name":"IDCEVO-25","full_name":"[Assigned ECU].[IDCEVO-25]"}],"bookmarked_members":[{"depth":1,"name":"IDCEVO-25","full_name":"[Assigned ECU].[IDCEVO-25]"}],"current_page_members":["[Assigned ECU].[IDCEVO-25]"]}]},"options":{},"view":{"current":"bar_chart","maximized":false,"bar_chart":{"stacked":false,"vertical":true,"swap_axes":false,"data_labels":true,"series_options":{"Open issues":{"type":"line"},"Average age days":{"separateAxis":2},"Average resolution days":{"separateAxis":2},"To Be Integrated":{"color":"#D4153B"},"Avg_Age_days_Integrated":{"type":"line"},"Avg_Age_Integrated":{"type":"line"}}},"table":{}},"calculated_members":[{"dimension":"Measures","name":"integrated+closed","formula":"(([Measures].[Issues resolved],\n [Status].[Integrated])\n +\n ([Measures].[Issues resolved],\n [Status].[Closed])\n)\n","format_string":""}],"description":"### Project - **IDCEVO Development**, Issue Type - **TAEEDefect**, Label - **BAT_blocker**, Assigned ECU - **IDCEVO-25**\n#### **To Be Integrated** - Issues with status \"Resolved\"in the corresponding week.\n#### **Integrated** - Issues with status \"Integrated\" in the corresponding week.\n#### **Avg_Age_days_Integrated** - Issues with status change from \"Resolved to Integrated\" in the corresponding week."}
} ],
"calculated_members": [{"dimension":"Measures","name":"Open issues","format_string":"#,##0","formula":"CASE WHEN [Issue].CurrentMember.Level.Name \u003c\u003e 'Issue' THEN\n Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [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"},{"dimension":"Measures","name":"Issues history","format_string":"#,##0","formula":"Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n [Measures].[Transitions to]\n - [Measures].[Transitions from]\n ))\n + [Measures].[Transitions to]\n - [Measures].[Transitions from]\n)"},{"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)"},{"dimension":"Measures","name":"Transition to status last date","format_string":"yyyy-mm-dd hh:nn:ss","formula":"TimestampToDate(\n [Measures].[Transition to status last timestamp]\n)"},{"dimension":"Time","name":"Last 12 weeks","format_string":"","formula":"Aggregate(\n [Time.Weekly].[Week].DateMembersBetween('12 weeks ago', 'today')\n)","dimension_hierarchy":"Weekly"},{"name":"Transition_Int","dimension":"Measures","formula":"([Measures].[Transitions to status issues count],\n [Transition Status].[Integrated])","format_string":""},{"name":"Integrated","dimension":"Measures","formula":"([Measures].[Issues history],\n [Transition Status].[Integrated])","format_string":""},{"name":"Avg_Age_days_Integrated","dimension":"Measures","formula":"CASE WHEN \n(\n[Measures].[Transitions to status],\n[Transition Status].[Integrated]\n) \u003e 0 \nTHEN\n AVG(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n (\n [Measures].[Transitions to status],\n [Transition Status].[Integrated]\n ) \u003e 0 \n ),\n DateDiffDays(\n (\n [Measures].[Transition to status first date],\n [Transition Status].[Resolved],\n [Time].CurrentHierarchy.DefaultMember\n ),\n (\n [Measures].[Transition to status last date],\n [Transition Status].[Integrated]\n )\n )\n )\nEND","format_string":""},{"name":"Avg_Age_Integrated","dimension":"Measures","formula":"CASE WHEN [Measures].[Issues resolved] \u003e 0 THEN\n AVG(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n DateInPeriod(\n [Issue].CurrentHierarchyMember.get('Resolved at'),\n [Time].CurrentHierarchyMember)\n ),\n CASE WHEN [Measures].[Issues resolved] \u003e 0\n THEN\n DateDiffDays(\n TimestampToDate((\n [Measures].[Transition to last timestamp],\n [Transition Field].[Status].[Integrated],\n [Time].CurrentHierarchy.DefaultMember\n )), \n [Issue].CurrentHierarchyMember.get('Resolved at')\n )\n END\n )\nEND","format_string":""},{"name":"To Be Integrated","dimension":"Measures","formula":"([Measures].[Issues history],\n [Transition Status].[Resolved])","format_string":""}]
}
