Hello,
I'm new to ServiceNow Reporting, I would like to get the average incidents per week for last 6 months period. I tried creating a report on the Incident table and tried selecting average on aggregation, but I couldn't get the average value of the incidents count. Is there a way I can get the average count of incidents per week.
Appreciate if you can give me a step by step suggestion.
Many thanks
Hema
Hi Hema,
Please select report type = column
and then add the following on the next tab:
or use plain count.
You should get something like this:
Hope this helps!
Regards,
Tudor
The average per week will be the sum per week, if that's what you want follow Tudor's great guide.
But if you want the average per week calculated by the formula (total INC in the last 6 months / total weeks in last 6 months), meaning the average based on the last 6 months total volume, then you need to use Performance Analytics.
Start by configuring an indicator source that gets all the incidents opened since 6 months ago. When we collect scores for this indicator source, the values in the filter will be dynamically replaced with values relative to the day we are collection for:
Here is an example of how the dates in the filter will be dynamic when the score for each date is collected:
And for the day before that one:
Then create an automated indicator based in the indicator source, using count aggregation:
Finally we'll set up a formula indicator to divide the number of incidents by the number of weeks. Sine 6 months will be 26 weeks, we could just divide by 26.
The other option is to use "score_end" which is a variable that contains the end of the date for each score. Since the indicator will contain scores for each date, the value for the indicators data point on 2021-01-01 will contain all the records opened between 2020-07-01 and 2021-01-01. The score end for that data point will be 2021-01-01 23:59:59.
What our formula is doing, by using javascript, is adding a negative amount of months to the score_end, thus getting a date exactly 6 months back in time. Then we get the time difference between score_end (now) and score_end 6 months back in time (then), in milliseconds. We then divide this time difference with the number of milliseconds in a week. This results in a decimal value for the number of weeks between those two dates. We use that to divide the number of incidents opened in that time.
This will give you an indicator that shows you the average value of incidents opened per week based in the sums of the 6 months preceding each data point:
The calculation for march 22, for example, will be based on how many incidents were opened between 2020-09-22 and 2021-03-22, divided by the number of weeks calculated from the difference between those dates in milliseconds. So you get a score for each date, a running score.
Note that my example data is pretty bad, I use demo data in which pretty much all incidents were opened after November 2020, which means it's only going up all the time as the number of opened incidents increases.
