The situation: End-of-Month KPI's Needed
It’s a standard feature in ServiceNow to be able to collect daily results for a Performance Analytics indicator and then aggregate that metric over a longer period of time. The available aggregation methods work well for “Occurrences-over-Time” type metrics, but are less than ideal for “Snapshot-in-Time” type metrics.
Many metrics tend to fall into 2 broad types, those that measure counts of a change over time, and those that measure a snapshot in time. Examples:
Occurrences-over-Time type metrics count the number of things that happened during a time period.
- Number of Incidents Opened each day
- Number of cases Closed each day
Snapshots-in-Time type metrics measure the number of items that exist as of a specific date / time.
- Number of Incidents in an open state as of the end of the day
- Number of projects which are open as of the end of the month
The standard aggregation methods provided as part of the Analytics Hub, or within Platform Analytics visualizations allow you to aggregate daily numbers into weekly or monthly results by summing or averaging the metrics from each day. For Occurrences-over-Time type metrics, these work well. It makes sense, for example, to sum the number of incidents opened on each day to get the number of incidents opened during the month. Averaging the number of incidents opened each day will give you the average daily opened incident count over each month. You can then plot these as month-by-month values.
Since Snapshots-in-Time metrics measure values as of a date, however, you frequently want report monthly values by comparing the value of the metric as of the last day of each month. This is especially true when measuring longer changes. For example:
- Number of users – comparing the count of users month over month
- Problem Tickets – Comparing the number of open problem tickets as of the end of each month
- Projects – Comparing the number of open projects as of the end of each month.
For Snapshot-in-Time metrics it is not logical to use Average or Sum to aggregate a daily KPI into a monthly KPI. Since there is not an appropriate time series aggregation, you need to create a new indicator which provides the month-end values. Ideally, you could set this up in advance as 2 indicators, a daily one from a daily indicator source, and a monthly one from an identical monthly indicator source. Sometimes, however, you have existing Snapshot-in-Time type metrics that have a significant amount of historical data points with breakdowns, and the only option is to create the Monthly indicator from the Daily Indicator.
A solution: Formula Indicators to the Rescue
At a high level, the solution is to use a formula indicator to calculate an end of month count from a set of end-of-day counts. This is made a bit more difficult by the fact that a formula indicator can’t change the frequency of a metric. If the base indicator is daily, then the resulting formula indicator will also be daily. My proposed solution therefore has 2 steps:
- Use a formula indicator to calculate metric that, for ever day in the month, has a result equal to the underlying metric as of the last day of the month.
- Use “By Month Average” to aggregate the daily results to a monthly result which is also equal to the value of the underlying metric as of the end of the month.
As an example, here is the underlying daily KPI reporting the number of projects which are in a given state as of the end of each day.
Step 1: Start with a metric which measures the daily value as of the end of each day. In this case the number of open projects as of the end of the day.
Create a new formula indicator – ie “Number of Projects – End of Month”. You can use this formula that for each day in the month, looks up the value of the PA indicator from the daily metric as of the last day of the month:
var gdt = new GlideDateTime(score_end);
var num = gdt.getDaysInMonthUTC();
gdt.setDayOfMonthLocalTime(num);
pa.getScore($[[Number of Projects - Daily]], gdt);
Replace “Number of Projects – Daily” with the Daily indicator you are working with.
Important: on your formula indicator, don’t forget to check “Apply Time Series to Result” on the “Other” tab, or the Time Series aggregation in the next step won’t work.
The resulting formula indicator shows daily numbers where for each day during the month the value is equal to the value as of the last day of the month.
Step 2: Create visualizations that aggregate the numbers (using By Month Average) to create a chart of the monthly end-of-month results.
Summary: Yes, it’s a workaround
This is definitely a workaround. I would love to see ServiceNow add a new standard available Time Aggregation that picks the value as of the end (or beginning) of each time period. “By Month – End of Month” and “By Week – End of Week” would be fantastic additions. Until then, this workaround is available.
Please jump in and reply if you know of a better workaround or other way to accomplish End of Month aggregation for Snapshot-in-Time metrics.
2 Comments
