MTTR based on MAX sub-task custom field (downtime) per parent

Questions & Answers
1

Hello!
I’m trying to create a report which will show me MTTR, but with a slight twist! We use Incident tickets and sub-tasks in our incident process. Each incident could have multiple Sub-tasks associated and they could all have different values (or they could be the same) in the custom field “Impact Downtime”

What I would like to be able to do is create a chart which shows the MTTR per month, based on the MAX value taken from the Impact Downtime field of all Sub-tasks associated with each parent incident ticket that has a P1 or P2 Sub-task associated.

I can create a report using the Issue dimension which shows me all incidents with a sub-task, by using the Sub-task ==> Parent hierarchy level member. I can also get that same report to show me the MAX value of the field Impact Downtime in this same report, on a per incident basis, (but this is obviously just in a list of incidents/sub-tasks) by using this Calculated Measure:

Max(
CascadingChildrenSet([Issue].CurrentHierarchyMember),
[Measures].[Inc-Impact Downtime in Minutes]
)

What I don’t know, is how to get to a report/chart which shows MTTR based on the MAX downtime per parent incident charted over a period of months.

I hope that’s clear…I’ve kinda sketched in out in Excel to visualise it a bit better…hopefully!

image
image895×353 22.9 KB

Thanks in advance for any help you can provide.
David

2

Hi @dwightman

You can try the formulas below, and I will explain the differences between both formulas, as I was not sure about some details regarding your use case.

I would recommend starting with creating a new calculated member in your Priority dimension, as you mentioned that only priorities P1 and P2 need to be taken into consideration.
You can aggregate the priorities as follows:

I’ve named this calculated member “P1 & P2 priorities”

Aggregate(
  {[Priority].[P1],
  [Priority].[P2]}
)

Solution 1:
The below formula will iterate through Parents and subtasks, returning the maximum value, if the priorities are P1 and P2. When used with the Time dimension, the date report will consider is going to be the Parent issue creation date. If you are grouping your report by month, the Parent issue was created in February, but subtasks were created in January, then the values for subtasks created in January will not be considered.

Sum(
Filter(
Descendants([Issue.Sub-task].CurrentMember, [Issue.Sub-task].[Parent]),
-- show on time for Parent creation date
DateInPeriod(
[Measures].[Issue created date],
[Time].CurrentHierarchyMember)
),
CASE
-- When downtime for subtasks is grater than Parent downtime and Priority is P1 or P2, 
-- then return the Max value for sub-task. Else - return Parent downtime  
WHEN
Max(
CascadingChildrenSet([Issue].CurrentHierarchyMember),
--Impact Downtime measure has to be used. 
([Measures].[Inc-Impact Downtime in Minutes],
[Priority].[P1 & P2 priorities])
) > 
--Impact Downtime property has to be used. In your case this might be named "Issue Impact Downtime", please double check
[Measures].[Issue Inc-Impact Downtime in Minutes]
THEN
Max(
CascadingChildrenSet([Issue].CurrentHierarchyMember),
([Measures].[Inc-Impact Downtime in Minutes],
[Priority].[P1 & P2 priorities])
)
ELSE
--Impact Downtime property has to be used. In your case, this might be named "Issue Impact Downtime", please double check"
[Measures].[Issue Inc-Impact Downtime in Minutes]
END)

Here is an example of the formula above. See the “Solution 1” column,
Parent issue was created on Feb 2, it has 3 subtasks, two of them were created on Feb 2, but one subtask (ETQ 27) was created on January 19, and it has the highest value for downtime (80 minutes). With the formula above, the returned value will be 60 instead of 80, because the formula only searches for values for reference using the month when the Parent issue was created.
​
​​
​
​Solution 2:
​The below formula will iterate through Parents and subtasks, returning the maximum value, if the priorities are P1 and P2. When used with the Time dimension, the date report will consider is going to be the Parent issue creation date. If you are grouping your report by month, the Parent issue was created in February; then the results will be returned in the month of February. If there is a subtask that was created in January and has the highest value, this value will be returned in the month of February (because that is when the Parent issue was created) against the Parent issue.
​

Sum(
  Filter(
    Descendants([Issue.Sub-task].CurrentMember, [Issue.Sub-task].[Parent]),
-- show on time for Parent issue creation date
    DateInPeriod(
      [Measures].[Issue created date],
      [Time].CurrentHierarchyMember)
    ),
    CASE
-- When downtime for subtasks is greater than Parent downtime and Priority is P1 or P2, 
-- then return the Max value for the sub-task. Else - return Parent downtime 
    WHEN
    Max(
CascadingChildrenSet([Issue].CurrentHierarchyMember),
-- Impact Downtime measure has to be used
([Measures].[Inc-Impact Downtime in Minutes],
-- Calculated members to aggrgate priorty P1 and P2. Disregarding time when subtasks were created.
[Priority].[P1 & P2 priorities],
[Time].CurrentHierarchy.DefaultMember)
) > 
-- --Impact Downtime property has to be used. In your case this might be "Issue Impact Downtime", please double check"
[Measures].[Issue Inc-Impact Downtime in Minutes]
THEN
    Max(
CascadingChildrenSet([Issue].CurrentHierarchyMember),
([Measures].[Inc-Impact Downtime in Minutes],
[Priority].[P1 & P2 priorities],
[Time].CurrentHierarchy.DefaultMember)
)
ELSE
[Measures].[Issue Inc-Impact Downtime in Minutes]
END)

​
​Here is an example of what value this formula will return. The Parent issue is created on Feb 2. When the formula is used with Time Dimension, the result will be returned in February (because that is when the parent issue has been created), but the value returned is from the subtask created in January, which has the highest Impact Downtime. Formula disregards the time when a sub-task is created.
​
​
​
I would recommend trying both formulas on your data (amend the formula where necessary) and see what fits best for your use case. I have added some comments in the formula, indicating where you have to use the measure and where property needs to be used. If you have named your calculated member in the Priority dimension differently, please adjust the formula for the Priority dimension accordingly as well.

Let me know if I missed something and if you have some further questions!

Elita from support@eazybi.com