I am trying to determine how many of our issues are resolved in 1 day or less. I’m getting results but I dont think its accurate and using this script and my user defined calculation, I can change the days and nothing in the results change
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: “Softchoice - resolved in 1 day”,
“folder_name”: “Softchoice ServiceDesk”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Total Issues]","[Measures].[1 day or less]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Time”,“selected_set”:["[Time].[Month].Members"],“members”:[],“bookmarked_members”:[{“depth”:3,“name”:“Nov 2019”,“full_name”:"[Time].[2019].[Q4 2019].[Nov 2019]",“drillable”:true}]}]},“pages”:{“dimensions”:[{“name”:“Issue Type”,“selected_set”:["[Issue Type].[Issue Type].Members"],“members”:[{“depth”:1,“name”:“Incident”,“full_name”:"[Issue Type].[Incident]"}],“bookmarked_members”:[],“current_page_members”:["[Issue Type].[Incident]"]},{“name”:“Project”,“selected_set”:["[Project].[SDS]"],“members”:[{“depth”:0,“name”:“SDS”,“full_name”:"[Project].[SDS]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Project].[SDS]"]}]},“options”:{“nonempty”:true},“view”:{“current”:“table”,“maximized”:false,“table”:{“freeze_header”:false}}}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue created date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Created at’)",“annotations”:{“group”:“Issue properties”,“predefined”:true}},{“dimension”:“Measures”,“name”:“Issue resolution date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Resolved at’)",“annotations”:{“group”:“Issue properties”,“predefined”:true}},{“dimension”:“Measures”,“name”:“Issue closed date”,“format_string”:“mmm dd yyyy”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Closed at’)",“annotations”:{“group”:“Issue properties”,“predefined”:true}},{“name”:“Total Issues”,“dimension”:“Measures”,“formula”:"[Measures].[Issues resolved]",“format_string”:""},{“name”:“1 day or less”,“dimension”:“Measures”,“formula”:“NonZero(Count(\n Filter(\n Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),\n (\n --closed\n (\n [Measures].[Issues resolved] \u003e 0\n --IsEmpty([Measures].[Issue closed date])\n AND\n DateDiffDays(\n [Measures].[Issue resolution date],\n now()\n ) \u003c= 1\n )\n OR\n --Closed\n (\n [Measures].[Issues closed] \u003e 0\n --NOT IsEmpty([Measures].[Issues closed date])\n AND\n DateDiffDays(\n [Measures].[Issue created date],\n [Measures].[Issue closed date]\n ) \u003c= 1\n )\n )\n )\n))”,“format_string”:""}]
}