How can I get a single score report that captures average daily incidents opened on a month to date basis.

[Monica Burgess]

I am trying create a single score report that is on a dashboard that shows the average count. I am looking for an average of incidents opened daily on a MTD basis and then average incidents opened monthly for the previous three months. Once I choose average as the aggregation the aggregated field options do not let me choose to do an average count on opened. Any suggestions would be appreciated. 

[froehre31]

Hello Adam!  Do you know of a way we can use the AVG by Month or Month to date AVG that would use only business days?  I have a request to update an indicator to show this but they only want me to average on business days vs the entire month.  

[DJL2]

Hi froehre31,

I asked a similarly themed 'business days only' question a little while back and the steer given was to create a schedule which declared the business schedule and use that to determine a window of time.  This was my post:

https://community.servicenow.com/community?id=community_question&sys_id=415a31c8db4d08182be0a851ca96...

So, not quite what you're trying to do - which essentially is for your average to divide by 20ish as opposed to 30ish, right?  I can well understand why your client would want to do that as the outcome will be very different!

I recall trying the script but ran into issues and reverted to Excel.

Would be useful to get a view from Adam on ways to approach this...

Thanks,

DJL

[Adam Stout]

You would need to build an indicator that counted the number of business days in a month.  For many companies, this is a slippery slope since.  Once we are removing weekends, we then want to remove holidays.  Once we remove holidays, we want to remove holidays for country X but not Y.  How do you count tasks that came in (or were completed) over the weekend?

Generally, I prefer to use calendar days since we don't have to deal with that.

If I had to do it, I would want to center the logic on Schedules.  Either using a job to populate a table with one record per business day (that I can then count) or perhaps do it on the fly using some of the Formula API methods.

[DJL2]

Thanks Adam 🙂

I know things like this introduces complications - but business is complicated, right; some work Mon-Fri, others Mon-Sat and a few are 24/7 - so the 'working week' is very dynamic and individual to each organisation.  But I think it's important to recognise and capture that individualism so that the data is much more representative.

Recently, one of our board members held a view of our daily Incident average which was 100% informed by the Ave displayed on their SNOW dashboard.  The problem was, of course, that the true Daily Ave was much higher than they'd built a number of business case assumptions around.  So when we talk about 'actionable data' it first needs to be accurate and representative, before expensive actions are taken.

As I said, I get it that this can be complicated.  But tools like the ServiceNOW platform are purposed and promoted as being able to tackle complexity and head it off at the pass.  And I get that there are possibly methods to do this - but the question I always listen to from my colleagues is often 'why isn't the option built into the platform?'.

Thanks,

DJL

[Adam Stout]

Leveraging Schedules, this can be done.  The hard part is spelling out the logic you want to use?  Assigned To schedule? (And what happens if it gets reassigned?) Caller's schedule?  Where the CI is located?  If you have the requirements spelled out, the system can handle it.  

The situation I come across more commonly is that the requirement is "Business Hours" but that what that means is not thought through nor consistent across the enterprise.  If you do have that thoroughly defined and agreed to, you are way ahead of the game.