Reporting Month-End Numbers from a Daily Snapshot-type Indicator

[Rob Greeley]

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:

1. 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.
2. 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.

[MattSomers]

@Rob Greeley ,

This is a great workaround, but I believe I found an OOB solution for this.  Perhaps it has come into play since October of 2024.

Within a formula indicator, there is a form section "Other."  That section contains 2 checkboxes: "Allow aggregation of multiple breakdown element scores" and "Apply time series to result."  By setting these to true on the formula indicator, scores can be aggregated on dashboards configured with an associated breakdown. When false, only separate scores are shown on dashboards configured with an associated breakdown.  You can also define the time series on the widget configuration, allowing you to reuse the daily formula indicator in each.

Once these setting are true, when I add a widget that calls the formula indicator, the individual widget can drive the aggregation and time series.  For instance, I have 2 widgets on a performance dashboard calculating the daily average of Incident SLAs that were successfully made.  I am using one indicator source of Incidents.SLA.Resolved within the 2 automated indicators and the formula indicator leveraging the 2 automated indicators to get my daily average.  Within the widget settings, I call my formula indicator, set my breakdown, and select my time series specific to the widget.

I can also click on the widget form section "Column Settings" and display multiple scores.  For weekly, I selected 4 periods and a period step of 1.  For monthly, I selected 12 periods and a period step of 1.  The output are widgets that are displaying my daily formula indicator scores by time series specific to the widget.  Some data is missing as no one is actively working Incidents in dev environment and we do not have recently cloned data.

Let me know if this also works for your use case!  Hopefully this can help some others out there as well.

[Dan Anthony]

Thank you for this, it's been a life saver. Could you advise the how to amend this to look at 'end of the week'?

var gdt = new GlideDateTime(score_end);

var num = gdt.getDaysInMonthUTC();

gdt.setDayOfMonthLocalTime(num);

pa.getScore($[[Number of Projects - Daily]], gdt);