Call resolution record widget

[James Bartle]

Hi, I was wondering if there was anyway to set up a call resolution record widget.

By this I mean having a widget that displays the top 5 people who have the resolved the highest amount of calls in a 7 day period. But showing this forever.

E.G.

John Smith has resolved 84 calls in a week at some point and Keith has resolved 76 calls in one week. These top 2 places will be displayed on a widget as these values forever (Unless they manage to beat that amount)

We like to have a bit of friendly competiton in the office so this would make it all the better.

Thanks.

[Guest Chaz]

Something to get you started, here's a query that will group the requests resolved by a user by week (year also taken into account), ordered by the most resolved

SELECT
	h_resolvedby_user_id,
	COUNT(h_pk_reference) as resolved_count,
	CONCAT(YEAR(h_datelogged), '/', WEEK(h_datelogged)) as year_week
FROM
	h_itsm_requests
WHERE
	h_resolvedby_user_id IS NOT NULL
GROUP BY
	h_resolvedby_user_id, year_week
ORDER BY
	resolved_count DESC

 

[James Bartle]

On 29/09/2017 at 10:56 AM, Chaz said:

Something to get you started, here's a query that will group the requests resolved by a user by week (year also taken into account), ordered by the most resolved

SELECT
	h_resolvedby_user_id,
	COUNT(h_pk_reference) as resolved_count,
	CONCAT(YEAR(h_datelogged), '/', WEEK(h_datelogged)) as year_week
FROM
	h_itsm_requests
WHERE
	h_resolvedby_user_id IS NOT NULL
GROUP BY
	h_resolvedby_user_id, year_week
ORDER BY
	resolved_count DESC

 

Hi Chaz,

How exactly would that be set up sorry?

I tried pasting it into the filter to no avail. The image attached is the way the options are set up. Would you be able to say how they should be in there at all?

Thanks

[Guest Chaz]

@James Bartle If you change the type of widget to 'List of Data', you'll then have the option to provide a custom SQL query

 

[James Bartle]

Hi Chaz,

Following that what specific things would I need to change in order to have the chart show up?

[Lyonel]

Small changes to the SQL if you want to focus on properly resolved calls, and limit to TOP 5

SELECT
	h_resolvedby_user_id,
	COUNT(h_pk_reference) as resolved_count,
	CONCAT(YEAR(h_dateresolved), '/', WEEK(h_dateresolved)) as year_week
FROM
	h_itsm_requests
WHERE
	h_resolvedby_user_id IS NOT NULL and h_dateresolved IS NOT NULL and h_reopencount = 0
GROUP BY
	h_resolvedby_user_id, year_week
ORDER BY
	resolved_count DESC
LIMIT 5

(includes a filter on calls resolved but not having been reopened!)

[Lyonel]

@James Bartle I really doubt with the current version of Analytics you would be able to do such widget (the chart bit of course, the list works just fine). Maybe worth having a look at PowerBi: https://wiki.hornbill.com/index.php/PowerBI_Reporting