Daily run widget with current month total

[Joyce]

Hi,

Currently for daily run measures/widget, based on a date (eg DateResolved), gives count on that day. 

I am looking for a way to get total count/ percentage within a current month (from start of month to date), of ticket resolved within SLA, but the widget should update/run daily.

So say, '80% of Tier 1 tickets, resolved from 1st May to date, have been resolved within SLA, '
Current i have the daily measure, with daily counts like this (see attached file)

Percentege resolved in a moth within fix.docx

Thanks.

Regards,

Joyce

 

[Guest]

Hi @Joyce

I've just been having a look into your request above, and unfortunately I don't believe its possible to create this using a measure or in a "List Of Counters" widget as I think you were after. There may be something further I can look into via a measure potentially but the best display if this would be a list of counters and this is where we have a slight restriction for percentages as we have developed a user friendly interface to help easily create the statements that is required - and that doesn't currently accommodate the more advanced calculation this would require. I will raise this internally to see if any of the developers have an idea on how this could be performed in the future. 

In the mean time, I was able to generate the SQL that I believe you need for this to be displayed in a "List of Data" widget with the custom SQL function. Although not ideal, this should show you the raw figures that you have asked for. It may need some testing on to ensure it brings back the right figures, but this appears to work on my data. I've posted some screenshots on how this was created below:

 

SQL Statement:

select COUNT(*) AS Number_Of_Incidents_Within_Fix, COUNT(*) / requests.total * 100 AS Percentage_Of_Incidents_Within_Fix
from h_itsm_requests,
(SELECT COUNT(*) AS total FROM h_itsm_requests where h_requesttype='Incident' and (h_status = 'status.resolved' or h_status='status.closed') and h_withinfix is not null and h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY) AS requests
where h_withinfix = 1 and h_requesttype='Incident'
and (h_status = 'status.resolved' or h_status='status.closed') and h_dateresolved >= LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY and h_withinfix is not null

 

I hope this helps

Kind Regards

Bob

[Joyce]

Hi Bob,

Thank you for the above. I have tested it in our environment. The count is correct, but the percentage is slightly different (correct value was 57. 89474 while value displayed on the widget is 59.4595. This could be due to our data.

Thanks

Regards,

Joyce

[Joyce]

Hi @Bob Dickinson

 

As a work around to this we have create 'list of counter' widget, as 'AVG', 'h_withinfix' from 'h_itsm_requests'  Where :h_requesttype='Incident' 
AND h_fk_servicename like '%t1%' 
AND (h_status = "status.resolved" OR h_status = "status.closed") 
AND h_dateresolved >= '2017-08-01 00:00:00' AND h_resolvedby_teamname ='Operations''.

The value from above, which should be average for Tier Incidents resolved from 01/08/2017 to date is completely different from taking a count of all T1 incidents  resolved from 01/08/2017 withinfix divided by total T1 incidents resolved from 01/08/2017 to date.

Please refer to two widget :

1. SLAM - SLA performance MTD- Month to Date - Operations Support 

1. SLAM - SLA Volume performance MTD- Month to Date - Operations Team

Can you advise, why these values are very different?

Regards Joyce

[Guest]

Hi @Joyce

The reason you are finding the difference in the stats is because of how SQL works out an average - and the fact that it does not account for null values in an average calculation. 

In your query, because you are looking at the calls to date (i.e. in August), there is very likely to be requests that are ongoing - they have not met or breached any SLA yet - so Hornbill does not put a 1 or a 0 in the column for these, its simply empty. This means that you actually have 3 different potential occurrences - 1, 0 or NULL. 

Now lets take an example:

Reference        h_withinfix
IN00000001    1
IN00000002    0
IN00000003    NULL (this is an ongoing request that has not yet met or breached SLA)
IN00000004    1

You might expect that if you were doing an average for the number of met (h_withinfix=1) requests it would end up at 50%:  Met Requests (2) divided by Total Sample (4) = 0.5
What ACTUALLY happens is that it does not include NULL values in the total sample - so the average would return 66.66%: Met Requests (2) divided by Total Sample (3) = 0.6666

This is what I believe is happening for you - its rightly ignoring the NULL values, giving you a higher percentage than what you are expecting. 

There is a way around this - if you would like NULL values to count as 0 (not breached) we can perform this substitution for the sake of the calculation using the COALESCE function as per the screenshot below. Using my example above, this would return the average as 50%.
(This function does NOT replace the values in the database from NULL to 0 - its purely used as part of the average calculation). 

 

Give this a try on the AVERAGE widget you have referenced above and see of this gives you a figure that you were expecting. 

Kind Regards

Bob

[Joyce]

Thanks @Bob Dickinson, I will try that, but this should only consider ''Resolved' and 'closed' Incidents (Where statement: h_requesttype='Incident'  AND  (h_status = "status.resolved" OR h_status = "status.closed") AND h_withinfix='1' AND h_dateresolved >= '2017-08-01 00:00:00' ) so technically there shouldn't be a null value?

Regards,

Joyce 

[Guest]

2 hours ago, Joyce said:

Thanks @Bob Dickinson, I will try that, but this should only consider ''Resolved' and 'closed' Incidents (Where statement: h_requesttype='Incident'  AND  (h_status = "status.resolved" OR h_status = "status.closed") AND h_withinfix='1' AND h_dateresolved >= '2017-08-01 00:00:00' ) so technically there shouldn't be a null value?

Regards,

Joyce 

Good point @Joyce

In which case, you should probably investigate why some of your Resolved or Closed Requests have a NULL value in the h_withinfix column, as if they have an SLA associated, I would expect this to be showing 0 or 1

Kind Regards

Bob