Monthly Measures Result discrepancies

[dwalby]

Hi all,

Each month we're required to produce KPI statistics for the following monthly key service desk metrics:

• No. of Incidents & Service Requests Logged
• No. of Incidents & Service Requests Resolved
• No. of Open Incidents & Service Requests (Recorded as of the last day, hour, minute of the month)

The problem I'm having is getting the number of open Incidents & Service Requests to correlate with the number logged and resolved. The results circled in red are those from April 2018 - unless my logic is incorrect the number of open Incidents and Service Requests for April should be: 

INC/SR - No. Open Per Month (End of March) = 652
+ INC/SR - No. Logged Per Month (April) = 1788
- INC/SR - No. Resolved Per Month TEST (April) = 1807

TOTAL = 633 

But as you can see the INC/SR - No. Open Per Month (End of April) is showing as 657.

I've included some screenshots of the measures as they're currently setup. Does anyone have any suggestions or advice on how to troubleshoot this?

 

Also, if I create a view within the request list to show all open incidents and service requests, the total open count this morning is showing 650 despite nothing being resolved between 30/04/18 11:59PM (when the measure finished) and now... So there's 7 incidents or services requests missing from the count captured in the measure.

So I've got 3 separate counts for open incidents and service requests:

633 by my calculations
657 by the No. of Open Incidents & Service Requests
650 by a custom view within the request list

 

Any help would be appreciated

[Steven Boardman]

@dwalby a couple of thoughts

1. The measure is looking at all requests in the database that meets your requirements, when you create a request list view via the user app, it will only return results on the requests you have the rights to view - so just checking that there are not teams / services you are not a member of, or maybe requests which are not assigned to any team / service - not saying this is the issue but maybe worth ruling out?

2. In your date resolved example is it possible you have had any requests which have been re-opened once resolved? so back in an open status? maybe include additional clause to say status is resolved closed?

Just some thoughts 

Steve

[dwalby]

@Steven Boardman I've amended the INC/SRs resolved per month to the below:

But now the results are returning the same value each month. I thought this would return no. closed calls in April, March, etc. with different values?

[Steven Boardman]

@dwalby yes i would expect the actual results per month as well (as i seeing on my instance) - i'll have to ask @Victor to assist as i don't have access to instances

[dwalby]

Thanks @Steven Boardman

[Victor]

@dwalby I'll have a look. As Steven mentioned above most likely I will have to look at the configurations in your instance. Let me know if ok or not ok for me to do so

[dwalby]

@Victor - Yes please proceed, thank you.

[dwalby]

@Victor - Were you able to access my instance OK?

[Victor]

@dwalby yes, sorry for the delay.... the numbers from your measures don't make much sense (for me)... I mean they are not right from what I can see... I need to investigate this a bit more....

EDIT: right, the issue lays with the "Query where clause" ... that needs to be put in brackets because the SELECT statement that does the counting for each month has some inherent criteria on which it adds whatever criteria is in "where clause" on measure configuration... is al about how OR and AND operator precedence works... Basically without brackets you are not counting everything that matches "closed date in that month and request type is incident" OR "request type is service request" ...as it is now, you are basically counting service requests (all SR that currently exist in your instance) plus incidents closed in the month... for each month... what it should count is "closed date in that month" AND ("request type is incident" OR "request type is service request"). The brackets will achieve this. Alternatively, in the where clause use this: h_requesttype IN ("Incident", "Service Request")

or

[Victor]

@dwalby by the way .. the above advice (brackets) applies for all measures where you have a WHERE clause comprised on more than one criterion... best to put these in brackets to make sure they are treated in addition to whatever inherent criteria the measure has...

[Steve Giller]

Would it be more efficient for the query builder to add brackets around the Query where clause for every measure?
It's not going to break anything and would assist those users who are not as familiar with SQL and precedence.

[Victor]

2 hours ago, DeadMeatGF said:

Would it be more efficient for the query builder to add brackets around the Query where clause for every measure?

I am sure it would...

[dwalby]

@Victor Thank you very much I'll check this out

[Victor]

Excellent! 

[dwalby]

@Victor - OK so  after re-sampling the number of requests logged for April has gone from 1788 down to 892. Just to confirm I'm understanding it right, that is because previously it was calculating ALL Incidents (Ever logged?) + Service Requests logged in the month?

On 5/8/2018 at 4:20 PM, Victor said:

@dwalby yes, sorry for the delay.... the numbers from your measures don't make much sense (for me)... I mean they are not right from what I can see... I need to investigate this a bit more....

EDIT: right, the issue lays with the "Query where clause" ... that needs to be put in brackets because the SELECT statement that does the counting for each month has some inherent criteria on which it adds whatever criteria is in "where clause" on measure configuration... is al about how OR and AND operator precedence works... Basically without brackets you are not counting everything that matches "closed date in that month and request type is incident" OR "request type is service request" ...as it is now, you are basically counting service requests (all SR that currently exist in your instance) plus incidents closed in the month... for each month... what it should count is "closed date in that month" AND ("request type is incident" OR "request type is service request"). The brackets will achieve this. Alternatively, in the where clause use this: h_requesttype IN ("Incident", "Service Request")

or

 

[Victor]

Just now, dwalby said:

Just to confirm I'm understanding it right, that is because previously it was calculating ALL Incidents (Ever logged?) + Service Requests logged in the month?

Almost...   ... it was calculating ALL Service Requests (ever logged) + Incidents closed that month.

[dwalby]

@Victor - Sorry to revive this thread from the dead!

I'm still having issues with the monthly figures for logged, resolved & open. I have to report them to our board every month, but they're querying why the numbers don't add up.

The figures I'm expecting are:

31st August Open (523) + September 1st to 30th Logged (1230) - September 1st to 30th Resolved (1199)
= Open on 30th September (554)

However, as below the Open count for September is showing 525

Do you have any suggestions on how I can identify what is causing this discrepancy? I'm assuming it may be something like cancelled, re-opened, or similar but I'm not sure how I can confirm this.

Thanks in advance!

 

[Victor]

@dwalby - I'll have a look at this tomorrow. If I fail this, give me a nudge... It doesn't have to be a gentle nudge...

[dwalby]

@Victor I'll hold you to that...

[Victor]

@dwalby - because I can' recreate the open count at a specific date, I will only assume the reason why the discrepancy is because you're not counting for the requests reopened/month in the measures. For example, there are 11 requests which have a reopened date in Sept, which means they are included in September open count but they were (or could have been) closed/resolved at the Aug count. It "pollutes" numbers for open, resolved and closed between months... What I suggest is to add another criterion to these measures and discount any requests reopened:

h_reopencount = 0

 

[dwalby]

@Victor - Thanks for this, makes sense.

Presumably this would also count requests logged but then subsequently cancelled? If so, what would the criterion be to exclude those?

To be clear, which measures would the criterion you've suggested need to be added?

[Victor]

6 minutes ago, dwalby said:

Presumably this would also count requests logged but then subsequently cancelled?

I think your measures already exclude cancelled requests.

6 minutes ago, dwalby said:

To be clear, which measures would the criterion you've suggested need to be added?

All 3.

[dwalby]

@Victor - I applied the criterion as suggested and now that it's the 1st of the month the measures have just generated for October.

End of Sept Open = 525
+ Logged October = 1526
- Resolved October = 1546

The open count for October should be = 505 but the measure shows: 469

I have to report these figures to our board, which includes a CFO who has a very keen eye for figures, meaning I get a grilling every time because the figures don't add up  Any further assistance on this would therefore be greatly appreciated.
 

[Victor]

@dwalby - I need to look at the data in your instance to figure out the difference. Need an admin account API key for this (https://wiki.hornbill.com/index.php/API_keys). Can you create one please and PM to me?

[dwalby]

@Victor Sure, will do