Resolution time for SLAs minus on hold time

Questions & Answers
1

I’ve set up a table that shows me all the tickets that haven’t met a pre-defined service level agreement based on priority (e.g. 1 day to resolve blockers, 7 days for trivial). So far I’m able to display this for the resolution time based on work days. I also had to split tickets up into 2 categories, i.e. tickets with a due date and those without. For tickets with a due date the SLA doesn’t apply, but we check whether the due date was met or not.

What I want to accomplish, but so far haven’t been able to, is to also subtract the time that a ticket has been put on hold (status is either hold, waiting or need more information) and thus only show the time that a ticket was being actively worked (any other status). How can I accomplish this?

For instance, to get the number of tickets that meet an SLA of 1 day for blockers:

CASE

WHEN  [Priority].CurrentMember.Name MATCHES 'Blocker' THEN

  NonZero(Count(

Filter(

  Filter(

      Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),

     [Measures].[Issues resolved] > 0 AND

[Measures].[Issues with due date] > 0) ,

 

  DateDiffDays([Issue].CurrentMember.get('Due date'),

[Issue].CurrentMember.get('Resolved at')) <= 1

)

  ))

END

Can I modify this by including the time in certain statuses (hold+need more information+waiting) and if so how? Thanks!

2

Hi,

You may want to subtract the time issue spend in these 3 transition statuses.

Please make sure you have imported issue change history and then try the following code for your calculated member (please double check the correct status member names in that code).

CASE
WHEN  [Priority].CurrentMember.Name MATCHES 'Blocker' 
THEN
NonZero(
Count(
  	Filter(
    	Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    	Not IsEmpty([Measures].[Issue due date])
    	AND
    	Not IsEmpty([Measures].[Issue resolution date])   	
    	AND
    	[Measures].[Issue Priority] = "Blocker"
    	AND
		(
		DateDiffDays(
  				[Issue].CurrentMember.get('Due date'),
				[Issue].CurrentMember.get('Resolved at')
			) 
			-
			(
				[Measures].[Days in transition status],
				[Transition Status].[Hold]
			)
			-
			(
				[Measures].[Days in transition status],
				[Transition Status].[Waiting]
			)
			-
			(
				[Measures].[Days in transition status],
				[Transition Status].[Need more information]
			)
		) <= 1
    	AND
    	[Measures].[Issues resolved] > 0 
	)
)
)
END

Kind regards,
Martins / eazyBI team

1 Like
3

I have a similar ask to this, but with a little bit different context. For all issues resolved/closed in a given period, I want to see the average workdays from created to resolved/closed including the history from previous periods minus workdays spent in Need more information including history from previous periods if the closed issue overlaps multiple periods. I tried adjusting the above calculated member code, but I can’t seem to get it to work. Any assistance you could provide would be great. Thanks.

4

Hi,

In this case, you could try the following code.
It should calculate the average difference in workdays between issue created date till resolution date minus workdays spent in “Need more information” status only for issues that were closed in the given time period (regardless which month they were created at).

NonZero(
Avg(
  	Filter(
    	Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      DateInPeriod(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember
      )
      and
      [Measures].[Issues resolved]>0
    ),
		DateDiffWorkDays(
  			[Issue].CurrentMember.get('Created at'),
				[Issue].CurrentMember.get('Resolved at')
			) 
			-
			(
				[Measures].[Workdays in transition status],
				[Transition Status].[Need more information]
			)
	)
)

I hope it helped!

Best regards,
Martins / eazyBI team

1 Like
5

Hi @martins.vanags.
Thanks for the response. I tried the code, but interestingly for every period it shows the average Resolution time as 1.000. As much as I’d like that to be the case for my average resolution time, I know that’s not an accurate value. I’m still looking into why it’s not working.

6

Hi,

Could you please share the definition of your report (after you saved latest changes)?
https://docs.eazybi.com/display/EAZYBI/Create+reports#Createreports-Exportandimportreportdefinitions

Best regards,

7

Hi @martins.vanags

I am attempting to do the same thing as Istarr. Have you been able to make any progress on this? I kept getting 1 for all issues as well.

Thanks in advance for your assistance.

8

Hi,

Please export and share your current report definition.
https://docs.eazybi.com/display/EAZYBI/Create+reports#Createreports-Exportandimportreportdefinitions

Best regards,
Martins / eazyBI team

9

{
“cube_name”: “FEP Projects”,
“cube_reports”: [ {
“name”: “Test 1”,
“folder_name”: “FEP Metrics”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Measures”,“selected_set”:["[Measures].[Issue created date]","[Measures].[Issue resolution date]","[Measures].[Issue status]","[Measures].[SLA1]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Issue”,“selected_set”:["[Issue].[Issue].Members"],“members”:[],“bookmarked_members”:[]}],“filter_by”:{“conditions”:[{“expression”:["[Measures].[Issue created date]"],“operator”:">=",“value”:“3/12/2018”,“value_type”:“date”,“value_format_string”:“mmm dd yyyy”},{“expression”:["[Measures].[Issue status]"],“operator”:"=",“value”:“Closed”}]},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“IT Team”,“selected_set”:["[IT Team].[All IT Teams]"],“members”:[{“depth”:0,“name”:“All IT Teams”,“full_name”:"[IT Team].[All IT Teams]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“FEP - Rescue Squad”,“full_name”:"[IT Team].[FEP - Rescue Squad]",“parent_full_name”:"[IT Team].[All IT Teams]"}],“bookmarked_members”:[],“current_page_members”:["[IT Team].[FEP - Rescue Squad]"]}]},“options”:{},“view”:{“current”:“table”,“maximized”:false,“table”:{}}}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issue created date”,“format_string”:“yyyy-mm-dd hh:nn:ss”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Created at’)",“annotations”:{“group”:“Issue properties”}},{“dimension”:“Measures”,“name”:“Issue resolution date”,“format_string”:“yyyy-mm-dd hh:nn:ss”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Resolved at’)",“annotations”:{“group”:“Issue properties”}},{“dimension”:“Measures”,“name”:“Issue status”,“format_string”:"",“formula”:"[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Status ID’)\n)",“annotations”:{“group”:“Issue properties”}},{“name”:“SLA1”,“dimension”:“Measures”,“formula”:“NonZero(\nAvg(\n \tFilter(\n \tDescendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),\n DateInPeriod(\n [Issue].CurrentMember.get(‘Resolved at’),\n [Time].CurrentHierarchyMember\n )\n AND\n\t\t(\n\t\tDateDiffWorkDays(\n \t\t\t[Issue].CurrentMember.get(‘Created’),\n\t\t\t\t[Issue].CurrentMember.get(‘Resolved at’)\n\t\t\t) \n\t\t\t-\n\t\t\t(\n\t\t\t\t[Measures].[Workdays in transition status],\n\t\t\t\t[Transition Status].[In Review]\n\t\t\t)\n\t\t\t\n\t\t)\n\t)\n)\n)”,“format_string”:""}]
}

10

@martins.vanags

Please see the report definition

11

Note I updated my previous post with the code.
Please try it now!

Br,

12

Please try the following code for calculated member “SLA1”

NonZero(
Avg(
  	Filter(
    	Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
      DateInPeriod(
      [Issue].CurrentMember.get('Resolved at'),
      [Time].CurrentHierarchyMember
      )
      and
      [Measures].[Issues resolved]>0
    ),
		DateDiffWorkDays(
  			[Issue].CurrentMember.get('Created at'),
				[Issue].CurrentMember.get('Resolved at')
			) 
			-
			(
				[Measures].[Workdays in transition status],
				[Transition Status].[In Review]
			)
	)
)

And please ensure you have imported issue change history via eazyBI import settings.

Best regards,
Martins

2 Likes