we are using ticketing system to track performace of out tools. We have “n” Customers and each one has “m” tools. I would like to calculate average MTTR globally and per each Customer and display it in a table.
To track MTTR our tickets type is “Incident” and has fields “Start downtime” and “End dowtime” which are filled in format dd/mm/yyyy hh:mm.
Tentative to caclulate average MTTR is:
AVG([Issue Type].[Incident], DateDiffDays([Measures].[Issue Start downtime],[Measures].[Issue End dowtime]))
with Formatting of type “Decimal”
Seems pretty straight forward, but I don’t see any numbers…I’m using table view with Columns=Measures and Rows=Tool
The Avg function works on a set of entities. The eazyBI app might use various dimensions for calculating the average of its members.
Therefore, you need to specify or filter the set of members for which to calculate the average value. You might see the actual syntax of the Avg here - Avg.
The below MDX expression checks through the Issue dimension to identify the relevant Issues and then calculate the average.
Avg(
Filter(
--generate the set of all issue level children for the current member of the Issue dimension
DescendantsSet([Issue].CurrentHierarchyMember, [Issue].[Issue]),
--the filtering conditions
--condition - relevant issue type
[Measures].[Issue type] = 'Incident'
AND
--downtime start populated
NOT IsEmpty([Issue].CurrentHierarchyMember.get('Start downtime'))
AND
--downtime ended in relevant time period
DateInPeriod([Issue].CurrentHierarchyMember.get('End downtime'),[Time].CurrentHierarchyMember)
),
--the actual figure for the average if relevant to context
CASE WHEN
([Measures].[Issues created],
[Time].CurrentHierarchy.DefaultMember)>0
THEN
DateDiffDays([Measures].[Issue Start downtime],[Measures].[Issue End downtime])
END
)
However, if you have a high number of issues and a multitude of customers*tools, then it is better to define a new JavaScript calculated custom field ‘Downtime duration’ for the relevant issue type and import it as a measure.
You may find out more about JavaScript calculated customfields here - JavaScript calculated custom fields.
In that case, the calculation of the average might be even as simple as this.
CASE WHEN [Measures].[Issues with Downtime duration]>0
THEN
[Measures].[Downtime duration]/[Measures].[Issues with Downtime duration]
END
You might also read about a similar use case in the following community thread - Time between to actions.
The required calculation for the specific use case takes into account the TTR for the issues falling within the specific filter condition - issue type = Incident and the rest of the report context.
The Average TTR is calculated for all issues returned by the filter and report context for the specific table cell.
It is not a cumulative figure of MTTR for displayed members up to the displayed member.
Thank you for your guidance. Now the question that came to me is that I put the EazyBI Time report (by quarter) and the issues in the Rows section, and by removing all issues, the issues were categorized in their desired quarters. Now I want to calculate this average TTR for the quarterly time frames and for the TTR of the issues within that quarter. How is the desired filter for this work?
The missing point is the actual relation between the issues and the Time dimension.
The issue might be created, resolved, closed, due, transitioned to a status, completed the SLA cycle, assigned to a user, or might have experienced a multitude of activities within the Time period.
You need to define what activity creates the primary bond between issue and Time. That timestamp is then used to determine the issue relation and filter out relevant issues.
Sometimes, the issue gets reassigned or otherwise changed (issue type or priority) after the completion of the SLA cycle. Then, the assignee (or other context) during the SLA cycle and the current assignee (or different dimensions) might differ.
A proper calculation needs to consider the current and the actual report context and the workflow within the company.
The following three options each have a different expression for the issue filter.
MTTR for issues that were created within the quarter and are now assigned to the specific user
MTTR for issues that were created by a specific user (group) anytime and were assigned to a particular user within a quarter and are resolved anytime
MTTR for issues that were created anytime and were resolved by anyone anytime but had their SLA cycle completed within the quarter before the change of issue type, reported split by their current issue type
Each dimension used in the report impacts the current and historical context. All these details have to be taken into account when creating the expression. The more complex the conditions, the more complex and slow the calculation.
Please decide on the planned report layout and dimensions used in the report, then define the relation (current or historical) of the dimensions to the issue, and then you can create the proper calculation.