Performance Analytics - Need help with metric indicators

[Delta007]

Hi Community. I am new in PA, and I got some requirements that I am unsure of. I need to create a report/indicator with the formula = [ age of all open incidents / total open incidents]. 
The age of all open incidents = current date - create date (in days).

I guess I need to use the formula indicator for this, but I am not sure how to calculate the age of open incidents and total open incidents. Do I need to create new indicators for them too? If so, which indicators will be best suited for this? 

Any kind of help is very much appreciated.

Thank you.

[Pavan Srivastav · Accepted solution]

Hello,

Welcome to Performance Analytics! Your instinct is correct — you do need a Formula Indicator, and it does require building the component indicators first. Let me walk you through the whole thing from scratch.

---

What You Are Building

Formula Indicator (Average Age of Open Incidents)
        │
        ├── Indicator A: Total Age of Open Incidents  (sum of days open)
        │
        └── Indicator B: Total Open Incidents         (count)

The formula will be: A ÷ B

---

Step 1 — Indicator B: Count of Open Incidents

This is the simpler one so build it first.

Navigate to: Performance Analytics → Indicators → Create New

Field	Value
Name	Open Incidents - Count
Table	Incident [incident]
Type	Automated
Aggregate	Count
Filter	State is not Resolved AND State is not Closed
Direction	Minimize (fewer open incidents = better)
Unit	Records

This gives you a simple count of all currently open incidents — your denominator.

---

Step 2 — Indicator A: Total Age of Open Incidents

This is the more complex one. You need to sum the age in days across all open incidents. PA cannot directly subtract dates natively in an automated indicator, so you have two options:

Option A — Script Indicator (Recommended)

Navigate to: Performance Analytics → Indicators → Create New

Field	Value
Name	Open Incidents - Total Age in Days
Table	Incident [incident]
Type	Manual / Script
Script	See below

// Script Indicator — sums age in days across all open incidents
(function(indicator) {

    var totalDays  = 0;
    var now        = new GlideDateTime();

    var gr = new GlideRecord('incident');
    gr.addQuery('state', 'NOT IN', '6,7'); // 6=Resolved, 7=Closed
    gr.query();

    while (gr.next()) {
        var opened = new GlideDateTime(gr.getValue('opened_at'));
        var diff   = GlideDateTime.subtract(opened, now);
        // getDayPart() returns whole days
        totalDays += Math.abs(diff.getDayPart());
    }

    indicator.setValue(totalDays);

})(indicator);

⚠️ Match the state values to your instance — check your incident State choice list to confirm the numeric values for Resolved and Closed.

Option B — Calculated Field Approach

Add a calculated field u_age_in_days on the Incident table that stores the current age, then use a standard Sum automated indicator on that field. This is simpler to set up but adds a field to the table and requires a business rule to keep it current.

Option A is cleaner — no schema changes needed.

---

Step 3 — Formula Indicator: Average Age

Navigate to: Performance Analytics → Indicators → Create New

Field	Value
Name	Open Incidents - Average Age (Days)
Type	Formula
Direction	Minimize
Unit	Days

In the Formula field:

A / B

Then map the variables:

Variable	Indicator
A	Open Incidents - Total Age in Days
B	Open Incidents - Count

---

Step 4 — Data Collection Job

All three indicators need to be added to a Data Collector to populate historical data and run on schedule.

Navigate to: Performance Analytics → Data Collectors → Jobs

Either add your indicators to an existing scheduled job or create a new one:

Field	Value
Name	Open Incidents PA Collection
Run	Daily (recommended — adjust to your needs)
Indicators	Add all three indicators above

After saving, click Collect Data Now to populate the first values and verify everything is working before waiting for the scheduled run.

---

Step 5 — Verify Your Output

Run this in Scripts - Background to manually validate the expected result before trusting the PA indicator:

var totalDays = 0;
var count     = 0;
var now       = new GlideDateTime();

var gr = new GlideRecord('incident');
gr.addQuery('state', 'NOT IN', '6,7');
gr.query();

while (gr.next()) {
    var opened = new GlideDateTime(gr.getValue('opened_at'));
    var diff   = GlideDateTime.subtract(opened, now);
    totalDays += Math.abs(diff.getDayPart());
    count++;
}

gs.info('Total open incidents: ' + count);
gs.info('Total age in days:    ' + totalDays);
gs.info('Average age in days:  ' + (count > 0 ? (totalDays / count).toFixed(2) : 'N/A'));

Compare this output to what your Formula Indicator shows — they should match.

---

Summary of What to Build

#	Indicator	Type	Purpose
1	Open Incidents - Count	Automated / Count	Denominator — B
2	Open Incidents - Total Age in Days	Script	Numerator — A
3	Open Incidents - Average Age (Days)	Formula (A÷B)	Final metric

The key insight for PA beginners is that Formula Indicators never collect data themselves — they always depend on other indicators doing the actual data collection. You always build the components first, then combine them in the formula.

[Delta007]

Hi Pavan.

Thanks for your help. This works with some minor adjustments. I want to ask one more question. I have an interactive filter on the dashboard on which I displayed the report. The filter is on date. Say I want to know the incident age of Jan 2026, when I select the dates, nothing happens. I tried running the historic job but no data is pulled. If this is doesn't makes sense for my report, I understand but there are more PA reports for which I need to use the date filter. Am I missing something? How to get the historic data? 

[Pavan Srivastav]

Hello,

There might be two distinct underlying problems:

**1. Incident Age Report and Date Filter Mismatch**

The Incident Age metric is designed to reflect the current, real-time age of incidents. In other words, it tells you how old an incident is right now, rather than how old it was at a specific past date. Therefore, when you attempt to apply a date filter—for example, for January 2026—ServiceNow cannot provide a historical snapshot of incident ages for that period, since it doesn't retain such information. As a result, the report either returns no results or ignores the filter altogether. This isn't an error; it's simply the expected behavior for real-time metrics.

Here's a quick overview of how date filters interact with different report types:

- **Incident Age:** Date filters do not work, as this is a point-in-time metric with no historical data.

- **MTTR:** Date filters are supported, since MTTR is calculated using the resolved_at timestamp.

- **Open Incident Count:** Date filters may work, but only if Performance Analytics (PA) snapshots exist.

- **Incident Volume Created:** Date filters are fully supported, leveraging the sys_created_on field.

- **Backlog Trend:** Date filters require PA historical data.

**2. Performance Analytics Historic Data Not Loading**

The second issue is more actionable and relates to missing historic data in Performance Analytics. If your dashboard is not displaying historical scores, here's a systematic approach to diagnosing and fixing the problem:

- **Step 1: Confirm Snapshots are Enabled for the Indicator**

Navigate to Performance Analytics > Indicators and ensure that 'Collect scores' is checked, the frequency is set appropriately, and breakdowns are configured if you require filtered views. If snapshots were never collected, running historic jobs won't retroactively fill the gap.

- **Step 2: Verify Historic Collection Support**

Go to Performance Analytics > Data Collector > Jobs, locate the job for your indicator, and ensure 'Enable historic data collection' is checked. The historic data start date should be set to January 1, 2026 (or earlier), and the underlying table must include a valid date field like sys_created_on.

*Important:* Historic collection works only if the source records still exist in the table. It re-executes indicator queries against past data using date filters—it doesn't retrieve archived records.

- **Step 3: Run the Historic Job Correctly**

Trigger the historic collection job manually, specifying the desired date range (e.g., January 1–31, 2026) and matching the frequency to your indicator. Afterwards, review the run history for any errors, such as 'No records found,' 'Breakdown not found,' or 'Score already exists.' Address each error accordingly—toggle overwrite options, reconfigure breakdowns, or enable historic collection as needed.

- **Step 4: Confirm Scores Have Been Stored**

Check under Performance Analytics > Scores > All Scores, filtering by your indicator and the target date. If scores appear, your dashboard filter should function as intended. If not, revisit earlier steps to ensure the historic job ran successfully.

- **Step 5: Ensure Dashboard Widgets are Properly Linked**

On your dashboard, distinguish between PA Score widgets (which use PA scores and support date filters) and Report widgets (which depend on field mapping). For Report widgets, configure the interactive filter to match the report's date field, such as sys_created_on.

**Decision Tree for Troubleshooting**

- If using a PA Indicator widget, check if scores exist for the desired period. If not, run the historic collection job.

- If using a Report widget, ensure the report leverages sys_created_on or resolved_at. If it relies on calculated or real-time fields like Age, date filters will not work and the report needs to be redesigned.

**Best Practices for Incident Age Reporting**

If you want to know the average age of incidents during January 2026, consider the following approaches:

- **Option A:** Create a PA Indicator using a duration formula (AVG(resolved_at - sys_created_on)), filtering on incidents resolved between January 1 and January 31, 2026.

- **Option B:** Use a standard report with a calculated Duration field, filtering by resolved_at within January 2026, and displaying results as a bar or average chart.

These methods provide an accurate historical perspective on incident age for the specified period, with full date filter support.

**Summary Table**

- If the date filter doesn't work on the Age report, redesign using resolved_at duration.

- If the historic job runs but yields no data, enable historic collection and set the start date correctly.

Regards

Pavan