Join the #BuildWithBuildAgent Challenge! Get recognized, earn exclusive swag, and inspire the ServiceNow Community with what you can build using Build Agent.  Join the Challenge.

Platform analytics trend cases report by state

valesky
Tera Contributor

3 weeks ago

Hello,

I have a Platform Analytics dashboard, and I’ve been asked to create a report showing the monthly trend of cases by status. Specifically, I need to display:

  • The number of cases that were in "New" status each month (e.g., how many were in "New" status in August, in September, in October, etc.).

  • The number of cases that were in "Open" status each month.

  • The number of cases in "Awaiting Info" status each month.

  • The number of cases in "Resolved" status each month.

To achieve this, I followed these steps:

  1. I created an Indicator Source called "Case Indicator Source" using the Case table as fact table, with no conditions applied.

  2. Then, I created four Automated Indicators, one for each status:

    • New Cases Indicator

    • Open Cases Indicator

    • Awaiting Info Cases Indicator

    • Resolved Cases Indicator

    Each indicator uses the "Case Indicator Source" and has an additional condition filtering by status. The aggregation is set to Count.

  3. I added these four indicators into a Data Visualization widget on the dashboard.

However, the indicators are not working as expected. For example, the Open Cases Indicator shows counts for September that include cases created in October—cases that didn’t exist in September.

Where an I foing wrong?

Thanks for your help

 

 

 

0 Helpfuls
  • 367 Views
6 REPLIES 6

MaxMixali
Giga Guru

3 weeks ago

The issue is that Performance Analytics (PA) doesn’t automatically show “number of records that were in a given state during a past month” unless you collect snapshots over time or use Trend by Date Dimension correctly.

 

Here’s the correct and best-practice solution to make your monthly case status trend work properly and be reliable.

 

 

 

🧠 The core concept

 

 

PA doesn’t calculate historical state counts dynamically — it needs snapshots taken daily/weekly/monthly.

So, to show “How many cases were in New status in August, September, October”, you must either:

 

  1. Use PA data collection (snapshot) — recommended for dashboards, or

  2. Build a trend chart with historical state data (sys_audit / created/closed dates) — if you can’t use snapshots.

 

 

We’ll focus on option 1 since you already use PA indicators.

 

 

 

✅ Best Practice Configuration (Recommended)

 

 

 

1. Indicator Source (you already have it)

 

 

  • Name: Case Indicator Source

  • Table: sn_customerservice_case (or your case table)

  • Filter: none

  • Date field: Opened or Created

  • Frequency: daily (this matters for trend)

 

 

📌 The date field defines when the record “enters” the dataset.

 

 

 

2. Indicators (your 4 statuses)

 

 

You did this correctly:

 

  • New Cases Indicator → condition state = New

  • Open Cases Indicator → condition state = Open

  • Awaiting Info Cases Indicator → condition state = Awaiting Info

  • Resolved Cases Indicator → condition state = Resolved

  • Aggregation: COUNT

  • Breakdown: none (unless you need region, priority, etc.)

 

 

 

 

3. Enable Daily Data Collection

 

 

Without data collection, the indicator only shows the current count.

 

  • Go to Performance Analytics > Data Collector > Data Collection Jobs

  • Create a new job:

     

    • Name: Case Status Indicators

    • Frequency: Daily

    • Indicators: Select your 4 indicators

    • Run as: pa.admin or admin

    • Active: ✅

     

  • Save and run it manually once → this will create your first snapshot.

 

 

After a few days/weeks, you’ll have trend data.

 

 

 

4. Trend Aggregation (Monthly Trend)

 

 

To show monthly results:

 

  • In the Data Visualization widget, set Time series → Aggregation = Monthly.

    (You can also use the Time Series widget instead of “Data Visualization” if you want native PA charting.)

  • Dimension: Score Date (the snapshot date)

  • Values: the Indicator Scores

 

 

✅ Now the chart will roll up daily snapshots into monthly counts.

 

 

 

5. Visualization setup

 

 

In UI Builder (or classic Dashboard):

 

  • Use a Data Visualization (Time Series) widget.

  • Data source: your 4 indicators (each as a separate line or stacked bar).

  • X-axis: Score Date (Time)

  • Y-axis: Count

  • Aggregation: Monthly

  • Legend: Show each indicator name (New, Open, Awaiting Info, Resolved)

 

 

 

 

⚙️ Advanced options

 

 

 

A. Use “State at time of snapshot”

 

 

In your Indicator Source, ensure “Collect records even if unchanged” is enabled, so PA re-evaluates state daily.

Otherwise, once a record leaves “New”, it won’t appear again in future snapshots.

 

This ensures your monthly count reflects “how many were in that state on that date” — not just newly entered cases.

 

 

B. Baseline / Historical load

 

 

If you need history before today, use “Historical Data Collection”:

 

  1. Go to Performance Analytics > Data Collector > Historical Data Collection

  2. Select your indicators and a date range (e.g., last 6 months)

  3. Run the job — ServiceNow will simulate daily snapshots.

Then you can immediately display past months.

 

 

C. Validation check

 

 

Run this script in Background to confirm snapshot presence:

 

code js

var gr = new GlideRecord('pa_scores');
gr.addQuery('indicator.name', 'New Cases Indicator');
gr.query();
gs.info(gr.getRowCount() + ' snapshots found for New Cases Indicator.');

 

 

 

If count = 0 → the collection job hasn’t run yet.

MaxMixali
Giga Guru
In response to MaxMixali

3 weeks ago

 

Optional alternative (no PA license)

 

 

If you don’t have Performance Analytics Premium, you can still emulate it with a Trend Report:

 

  • Use Reports > New

  • Type: Time Series

  • Table: case

  • Field: Opened

  • Group by: state

  • Interval: Monthly

    This shows monthly counts of cases created per state at creation, not their status at month-end.

    To capture “status as of each month”, only PA snapshots can do that.

 

 

 

 

✅ TL;DR — Best Solution

 

 

Use Performance Analytics with daily snapshot collection for each “status-based” indicator.
Then display those indicators in a monthly time series chart.

 

Steps summary:

 

  1. ✅ Indicator Source (Cases)

  2. ✅ 4 Indicators (New/Open/Awaiting Info/Resolved)

  3. ✅ Data Collection Job → daily snapshots

  4. ✅ Historical Data Collection → backfill history

  5. ✅ Dashboard Widget → monthly aggregation

 

 

Result: a clear, reliable trend showing how many cases were in each status per month.

0 Helpfuls

valesky
Tera Contributor
In response to MaxMixali

3 weeks ago

Thank you very much for your usefull answer.

But i dont't have the field “Collect records even if unchanged” in the Indicator source table.

It should be in the table of indicator source (pa_cubes)?

0 Helpfuls

MaxMixali
Giga Guru
In response to valesky

3 weeks ago

Excellent observation — and yes, this confuses many ServiceNow Performance Analytics (PA) users, because the “Collect records even if unchanged” option used to exist in older PA versions and was later deprecated / renamed / relocated depending on your ServiceNow release and the indicator source type.

 

Let’s clarify it precisely 👇

 

 

 

🧭 1. The field

“Collect records even if unchanged”

— background

 

 

  • This checkbox controlled whether the data collector should reinsert all matching records for every collection run, even if the records had not changed since the last collection.

  • The purpose: allow monthly trend charts to show “0” values or counts even if no changes occurred (e.g., backlog remained constant).

 

 

 

⚙️ Table name

 

 

Yes — it lives in the Indicator Source table: ps_cubes

 

0 Helpfuls