Track issue aging profile per status

Hi,

I am trying to get an aging profile per ticket status to track the progress of our service desk.
Somthing like this:
image

In the example above I can deduct that issues created in march were on average only a half day in status new while in April this is 5 days. Meaning that our engineers do not pick up on tickets fast enough.

When trying to build this report, the time used in the report, it’s using the date of when a ticket transitioned into that status which is not what I need. I have found multiple related questions & answers here but none that look into the actual status per created ticket time.

Anyone here that can assist?

Hey @TieleDeclercq

Welcome to eazyBI community!

You can try creating a calculated measure (with decimal formatting) using this formula.

`NonZero(
  Avg(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
  DateInPeriod(
    [Measures].[Issue created date],
    [Time].CurrenthierarchyMember
  )
  )
  ,
  CASE WHEN
  ([Measures].[Issues created],
  [Transition Status].CurrentHierarchy.DefaultMember)
  >0
  THEN
  Sum((
    [Measures].[Days in transition status],
    [Time].CurrentHierarchy.DefaultMember
  ))
  +
    CASE WHEN
    [Measures].[Issues history] > 0
    THEN
    DateDiffDays(
      [Measures].[Issue status updated date],
      Now()
    )
    ELSE
    0
    END
  END
  )
)`

Martins / eazyBI

1 Like

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:
image

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

Hi @TieleDeclercq

We usually recommend using “Transition Status” dimension (not “Status”) for historical calculations with Time dimension. Try replacing Status with Transition status in rows and page filters for your report.

Martins / eazyBI