Count issues that have the response time met for each severity

Questions & Answers
1

Hi,

I’m trying to calculate the number of tickets that have the response time met.
The response time threshold is set upon ticket severity, thus I need to have this count based on severity.

I’m using the following measure:

Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
([Measures].[Issue created date]),
[Time].CurrentHierarchyMember
)
AND
(CASE WHEN
([Measures].[Transitions to status],
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
DateDiffMinutes(
[Measures].[Issue created date],
([Measures].[Transition to status first date],
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember)
)
END
) < 120
)
)

this returns correctly the number of tickets for which the time spent before going to transition “waiting for customer” is less than 120 minutes.

I would need to include in the same measure the differente cases, and have the same count upon the different severities, for which the response time if not the same.

I have tried something like:

Count(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
([Measures].[Issue created date]),
[Time].CurrentHierarchyMember
)
AND
(CASE WHEN [Incident_Severity].CurrentMember.Name = ‘Critical’
THEN
(CASE WHEN
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember) > 0
THEN
DateDiffMinutes(
[Measures].[Issue created date],
([Measures].[Transition to status first date],
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember)
)
END)
END) > 30
)
)

which again looks ok on the count that I get,

however, I’d like to have all possible cases inside the same measure, I mean, being able to add the count for all others values for the severity, but I didn’t succeed to get it.

I’d be happy to have your thoughts and suggestions

thanks,
alexandre

2

Yes, you can add the validation by issue severity within the formula. Here I updated the formula for this using several different severities and limits for each:

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
([Measures].[Issue created date]),
[Time].CurrentHierarchyMember)
), 
CASE WHEN
DateDiffMinutes(
[Measures].[Issue created date],
([Measures].[Transition to status first date],
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember)
) < 
case Cast([Measures].[Issue severity] as string)
when "Critical" then 30
when "High" then 240
when "Medium" then 480
else 600
end
THEN [Measures].[Issues created]
END
)

Daina / support@eazybi.com

3

Thank you Daina,

That’s pretty nice and I have tried it and it works properly. Much appreciated!

I have a question that I have asked you on the other post as well, is there a way to check if a transition to a status exists.
I want to calculate the time to restoration only for the issues that were passed in transition “restored” and I would like to first check this happened.

is that possible?

thanks
alexandre

4

You can use a tuple with measure Transitions to status and a particular Transition status. To check if an issue moved to the status. You can add additional limitations to the tuple as well. I would suggest using exactly the same members in a tuple.

If there is not any other context you would like to compare, you can use the same measure as a filter. In this case, while the condition comparing if time spent is less than specified could work for issue with no transition to the particular status yet. If you use a related measure as a counter it will work as a filter as well.

Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
DateInPeriod(
([Measures].[Issue created date]),
[Time].CurrentHierarchyMember)
), 
CASE WHEN
DateDiffMinutes(
[Measures].[Issue created date],
([Measures].[Transition to status first date],
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember)
) < 
case Cast([Measures].[Issue severity] as string)
when "Critical" then 30
when "High" then 240
when "Medium" then 480
else 600
end
THEN 
-- this formula works as counter and as filter as well:
NonZero(([Measures].[Transitions to status issues count],
[Transition Status].[Waiting for customer],
[Time].CurrentHierarchy.DefaultMember))
END
)

Daina / support@eazybi.com