I am trying to create an report for issues created vs resolved and Open issues with time dimension in the rows.
Instead of Resolution, I want to consider whenever stats moved to Approved status considered as resolved. Can you please provide me the mdx measure, where I can get the number of issues moved to Approved status on that particular date?
Report Definition:
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Test Instance Created vs Resolved Report (WIP AK)”,
“folder_name”: “LDC - Build - Testing (OQT/PQT)”,
“result_view”: “bar_chart”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:[“[Measures].[Issues created]”,“[Measures].[Issues Resolved ]”,“[Measures].[Open issues]”],“members”:[{“depth”:0,“full_name”:“[Measures].[Issues Resolved ]”,“format_string”:“#,##0”,“report_specific”:true,“name”:“Issues Resolved “,“calculated”:true}]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:[”[Time.Weekly].[2023]”,“[Time.Weekly].[2024]”],“selected_set_expression”:“DescendantsSet({{selected_set}}, [Time.Weekly].[Week])”,“members”:[{“depth”:2,“name”:“W14, Apr 03 2023”,“full_name”:“[Time.Weekly].[2023].[W14, Apr 03 2023]”,“drillable”:true,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:}],“filter_by”:{“conditions”:[{“expression”:“[Time.Weekly].CurrentHierarchyMember”,“operator”:“between”,“value”:“Mar 19 2023,Mar 13 2024”,“value_type”:“date”}]}},“pages”:{“dimensions”:[{“name”:“Time”,“duplicate”:true,“selected_set”:[“[Time.Weekly].[All Times]”],“members”:[{“depth”:0,“name”:“All Times”,“full_name”:“[Time.Weekly].[All Times]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“2023”,“full_name”:“[Time.Weekly].[2023]”,“drillable”:true,“parent_full_name”:“[Time.Weekly].[All Times]”},{“depth”:1,“name”:“2024”,“full_name”:“[Time.Weekly].[2024]”,“drillable”:true,“expanded”:false,“drilled_into”:false,“parent_full_name”:“[Time.Weekly].[All Times]”}],“bookmarked_members”:,“current_page_members”:[“[Time.Weekly].[2023]”,“[Time.Weekly].[2024]”]},{“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”:“Test Instance”,“full_name”:“[Issue Type].[Test Instance]”,“parent_full_name”:“[Issue Type].[All Issue Types]”}],“bookmarked_members”:,“current_page_members”:[“[Issue Type].[Test Instance]”]},{“name”:“Test Case Type”,“selected_set”:[“[Test Case Type].[All Test Case Types]”],“members”:[{“depth”:0,“name”:“All Test Case Types”,“full_name”:“[Test Case Type].[All Test Case Types]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:,“current_page_members”:[“[Test Case Type].[All Test Case Types]”]},{“name”:“Status”,“selected_set”:[“[Status].[All Statuses]”,“[Status].[OQT Statuses]”],“members”:[{“depth”:0,“name”:“All Statuses”,“full_name”:“[Status].[All Statuses]”,“drillable”:true,“type”:“all”,“expanded”:false,“drilled_into”:false,“removed”:false}],“bookmarked_members”:,“current_page_members”:[“[Status].[All Statuses]”]},{“name”:“Functional Domain”,“selected_set”:[“[Functional Domain].[LDC - Verticals]”,“[Functional Domain].[Commercial ]”,“[Functional Domain].[Finance \u0026 Procurement]”,“[Functional Domain].[Operations ]”,“[Functional Domain].[Solution]”,“[Functional Domain].[Data]”,“[Functional Domain].[Reporting \u0026 Analytics]”],“members”:[{“depth”:0,“name”:“LDC - Verticals”,“full_name”:“[Functional Domain].[LDC - Verticals]”,“calculated”:true,“drillable”:true,“dimension”:“Functional Domain”}],“bookmarked_members”:,“current_page_members”:[“[Functional Domain].[LDC - Verticals]”]},{“name”:“Workstream”,“selected_set”:[“[Workstream].[All Workstreams]”],“members”:[{“depth”:0,“name”:“All Workstreams”,“full_name”:“[Workstream].[All Workstreams]”,“drillable”:true,“type”:“all”}],“bookmarked_members”:,“current_page_members”:[“[Workstream].[All Workstreams]”]},{“name”:“Test Cycle”,“selected_set”:[“[Test Cycle].[All Test Cycles]”,“[Test Cycle].[R1_EDW]”,“[Test Cycle].[R1_S4]”,“[Test Cycle].[R1_ECC]”,“[Test Cycle].[R1_GRC]”],“members”:[{“depth”:0,“name”:“All Test Cycles”,“full_name”:“[Test Cycle].[All Test Cycles]”,“drillable”:true,“type”:“all”}],“bookmarked_members”:,“current_page_members”:[“[Test Cycle].[All Test Cycles]”]},{“name”:“Detected in Cycle”,“selected_set”:[“[Detected in Cycle].[All Detected in Cycles]”],“members”:[{“depth”:0,“name”:“All Detected in Cycles”,“full_name”:“[Detected in Cycle].[All Detected in Cycles]”,“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false}],“bookmarked_members”:,“current_page_members”:[“[Detected in Cycle].[All Detected in Cycles]”]}]},“options”:{“nonempty”:true},“view”:{“current”:“bar_chart”,“maximized”:false,“bar_chart”:{“stacked”:false,“vertical”:true,“swap_axes”:false,“data_labels”:false,“series_options”:{“Open test”:{“type”:“line”},“Issues Resolved “:{“color”:”#00875A”},“Issues Open “:{“color”:”#344563”,“type”:“line”,“separateAxis”:1},“Issues created”:{“color”:“#B3BAC5”},“Open issues”:{“type”:“line”},“Issues resolved”:{“color”:“#00875A”}}},“table”:{}},“calculated_members”:[{“dimension”:“Measures”,“name”:“Issues Resolved “,“formula”:“IIf(DateDiffDays(Now(),[Time.Weekly].CurrentMember.Name)\u003c=0,\n([Measures].[Issues history],[Transition Status].[Approved]),NULL)”,“format_string”:””},{“dimension”:“Measures”,“name”:“Issues Open “,“formula”:”[Measures].[Issues created]-[Measures].[Issues Resolved ]”,“format_string”:“”}]}
} ],
“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)”},{“name”:“Commercial “,“dimension”:“Functional Domain”,“formula”:”[Functional Domain].[Commercial]”,“format_string”:“”},{“name”:“Finance \u0026 Procurement”,“dimension”:“Functional Domain”,“formula”:“Aggregate({\n[Functional Domain].[Finance],\n[Functional Domain].[Procurement]\n})”,“format_string”:“”},{“name”:“Operations “,“dimension”:“Functional Domain”,“formula”:“Aggregate({\n[Functional Domain].[Manufacturing],\n[Functional Domain].[Operations],\n[Functional Domain].[QA],\n[Functional Domain].[TRD],\n[Functional Domain].[SCM],\n[Functional Domain].[PLM]\n})”,“format_string”:””},{“name”:“LDC - Verticals”,“dimension”:“Functional Domain”,“formula”:“Aggregate({\n[Functional Domain].[Commercial],\n[Functional Domain].[Finance \u0026 Procurement],\n[Functional Domain].[Operations],\n[Functional Domain].[PLM],\n[Functional Domain].[Solution],\n[Functional Domain].[Data],\n[Functional Domain].[Manufacturing],\n[Functional Domain].[QA],\n[Functional Domain].[SCM],\n[Functional Domain].[TRD],\n[Functional Domain].[Reporting \u0026 Analytics],\n[Functional Domain].[Unmatch],\n[Functional Domain].[(none)]\n})”,“format_string”:“”},{“name”:“OQT Statuses”,“dimension”:“Status”,“formula”:“Aggregate({\n[Status].[NO RUN],\n[Status].[Passed],\n[Status].[Failed],\n[Status].[Not Completed],\n[Status].[Approved]\n})”,“format_string”:“”},{“name”:“R1_EDW”,“dimension”:“Test Cycle”,“formula”:“Aggregate({\n[Test Cycle].[LDC_R1 OQ - EDW PtQ1],\n[Test Cycle].[LDC_R1 OQ - EDW PtQ2],\n[Test Cycle].[LDC_R1 OQ - EDW PtQ3],\n[Test Cycle].[LDC_R1 OQ - EDW PtQ4],\n[Test Cycle].[LDC_R1 OQ - EDW PtQ5],\n[Test Cycle].[LDC_R1 OQ - EDW PtQ6],\n[Test Cycle].[LDC_R1 OQ - EDW PtQ7]\n})\n”,“format_string”:“”},{“name”:“R1_S4”,“dimension”:“Test Cycle”,“formula”:“Aggregate({\n[Test Cycle].[LDC_R1 Late Development],\n[Test Cycle].[LDC_R1 OQ - PTQ1],\n[Test Cycle].[LDC_R1 OQ - PTQ2],\n[Test Cycle].[LDC_R1 OQ - PTQ3],\n[Test Cycle].[LDC_R1 Post GoLive],\n[Test Cycle].[LDC_R1 Pre GoLive]\n})”,“format_string”:“”},{“name”:“R1_ECC”,“dimension”:“Test Cycle”,“formula”:“Aggregate({\n[Test Cycle].[LDC_R1 ECC - PtQ1],\n[Test Cycle].[LDC_R1 ECC - PtQ2],\n[Test Cycle].[LDC_R1 ECC - PtQ3]})”,“format_string”:“”},{“name”:“R1_GRC”,“dimension”:“Test Cycle”,“formula”:“Aggregate(\n {[Test Cycle].[LDC_GRC_NonS4_PTQ3],\n [Test Cycle].[LDC_GRC_NonS4_PTQ2]})”,“format_string”:“”}]
}