Thank you @martins.vanags and apologies for the late reply. I have been busting my head over this trying to make this work, but I will need more assistance.
With the calculated measure you gave me, it shows me the number of days these tickets have been in their current status as you can clearly see here:
The number 30,85 reflects an average days in resolved state of tickets with the request type ‘blocked page’ that have been created in October. It does not show me how long these tickets were on ‘in progress’ or ‘new’, which is the information I need.
If I read your measure correctly, that seems to be the intent as you sum up the days in transition and the date it has been in this status so far.
The definition I now used:
{
"cube_name": "Issues",
"cube_reports": [
{
"name": "Ticket aging profile",
"result_view": "table",
"definition": {
"columns": {
"dimensions": [
{
"name": "Time",
"selected_set": [
"[Time].[Previous month]"
],
"members": [
{
"depth": 0,
"name": "Previous month",
"full_name": "[Time].[Previous month]",
"annotations": {
"group": "Default",
"predefined": "true"
},
"calculated": true,
"drillable": true,
"dimension": "Time",
"expanded": true,
"drilled_into": false
}
],
"bookmarked_members": []
},
{
"name": "Measures",
"selected_set": [
"[Measures].[Days in transition status v2]"
],
"members": []
}
]
},
"rows": {
"dimensions": [
{
"name": "Status",
"selected_set": [
"[Status].[In Progress]",
"[Status].[Resolved]",
"[Status].[New]",
"[Status].[Planned]",
"[Status].[Waiting for customer]",
"[Status].[Waiting for external]",
"[Status].[Waiting for approval]",
"[Status].[Waiting for implementation]"
],
"selected_set_expression": "DescendantsSet({{selected_set}}, [Status].[Status])",
"members": [],
"bookmarked_members": []
}
]
},
"pages": {
"dimensions": [
{
"name": "Time",
"duplicate": true,
"selected_set": [
"[Time].[Previous month]"
],
"members": [
{
"depth": 0,
"name": "Previous month",
"full_name": "[Time].[Previous month]",
"annotations": {
"group": "Default",
"predefined": "true"
},
"calculated": true,
"drillable": true,
"dimension": "Time"
}
],
"bookmarked_members": [],
"current_page_members": [
"[Time].[Previous month]"
]
},
{
"name": "Status",
"duplicate": true,
"selected_set": [
"[Status].[All Statuses]"
],
"members": [
{
"depth": 0,
"name": "All Statuses",
"full_name": "[Status].[All Statuses]",
"drillable": true,
"type": "all",
"expanded": true,
"drilled_into": false
},
{
"depth": 1,
"name": "In Progress",
"full_name": "[Status].[In Progress]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "Resolved",
"full_name": "[Status].[Resolved]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "New",
"full_name": "[Status].[New]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "Planned",
"full_name": "[Status].[Planned]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "Waiting for customer",
"full_name": "[Status].[Waiting for customer]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "Waiting for external",
"full_name": "[Status].[Waiting for external]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "Waiting for approval",
"full_name": "[Status].[Waiting for approval]",
"parent_full_name": "[Status].[All Statuses]"
},
{
"depth": 1,
"name": "Waiting for implementation",
"full_name": "[Status].[Waiting for implementation]",
"parent_full_name": "[Status].[All Statuses]"
}
],
"bookmarked_members": [],
"current_page_members": [
"[Status].[In Progress]",
"[Status].[Resolved]",
"[Status].[New]",
"[Status].[Planned]",
"[Status].[Waiting for customer]",
"[Status].[Waiting for external]",
"[Status].[Waiting for approval]",
"[Status].[Waiting for implementation]"
]
},
{
"name": "Request Type",
"selected_set": [
"[Request Type].[All Request Types]"
],
"members": [
{
"depth": 0,
"name": "All Request Types",
"full_name": "[Request Type].[All Request Types]",
"drillable": true,
"type": "all",
"expanded": true,
"drilled_into": false
},
{
"depth": 1,
"name": "Blocked page",
"full_name": "[Request Type].[Blocked page]",
"parent_full_name": "[Request Type].[All Request Types]"
}
],
"bookmarked_members": [],
"current_page_members": [
"[Request Type].[Blocked page]"
]
}
]
},
"options": {},
"view": {
"current": "table",
"maximized": false,
"table": {}
},
"calculated_members": []
}
}
],
"calculated_members": [
{
"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": "Issue created date",
"format_string": "mmm dd yyyy",
"formula": "[Issue].CurrentHierarchyMember.get('Created at')"
},
{
"dimension": "Measures",
"name": "Issue status updated date",
"format_string": "mmm dd yyyy",
"formula": "[Issue].CurrentHierarchyMember.get('Status updated at')"
},
{
"dimension": "Time",
"name": "Previous month",
"format_string": "",
"formula": "Aggregate({\n [Time].[Month].CurrentDateMember.PrevMember\n})"
},
{
"name": "Days in transition status v2",
"dimension": "Measures",
"formula": "NonZero(\n Avg(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n DateInPeriod(\n [Measures].[Issue created date],\n [Time].CurrenthierarchyMember\n )\n )\n ,\n CASE WHEN\n ([Measures].[Issues created],\n [Transition Status].CurrentHierarchy.DefaultMember)\n >0\n THEN\n Sum((\n [Measures].[Days in transition status],\n [Time].CurrentHierarchy.DefaultMember\n ))\n +\n CASE WHEN\n [Measures].[Issues history] > 0\n THEN\n DateDiffDays(\n [Measures].[Issue status updated date],\n Now()\n )\n ELSE\n 0\n END\n END\n )\n)",
"format_string": "#,##0.00"
}
]
}
