How to calculate difference between two date type of fields in reporting?

[ujjwala_678]

Hello experts,

I am working on one of the requests related to performance analytics

I have to show average of difference between two fields created on and closed at

The report should show last 30 days data in single score days format

Along with that I have to create

1.average incident lifespan 

2.average time on new state

3.aveg time on work in progress state

Interactive filter for---category and subcategory these should be dependent on each other 

Reports need to be interactive with this

Pls guide me on this.... I think PA should work here.

Thank you

Ujjwala 

[fawadalam4514]

Hi Ujjwala,

Absolutely, Performance Analytics (PA) is indeed the right tool for this task in ServiceNow. I'm happy to guide you through this process. Here's a breakdown of what you're looking to achieve:

Average Difference Between Created On and Closed At:

You can create a calculated field on the incident table that calculates the difference between "Created On" and "Closed At" in days or hours, depending on your preference.
Next, create a Performance Analytics widget that averages this field and filters data from the last 30 days.
Average Incident Lifespan:

This could be similar to the first metric, especially if the lifespan of an incident is from creation to closure.
Average Time in States:

For each state (e.g., New, Work in Progress), you need to determine the time an incident spends in that state. This can be achieved by checking the incident audit/history logs.
You'll need to calculate the duration for each state transition and then create PA widgets to average those durations.
Interactive Filters:

For Category and Subcategory:
Create a new Interactive Filter.
Add the 'Category' field. This should be straightforward as it's a direct field on the Incident table.
Add the 'Subcategory' field. You'll want to set a dependency here such that it only shows Subcategories relevant to the chosen Category.
Interactive Reports:

Once you have your interactive filter, ensure your reports/widgets are interactive-compatible (e.g., no direct conditions that conflict with the filter).
Attach the interactive filter to the reports, and they will become filterable based on your category/subcategory selection.
Here are some key steps:

Setting up Calculated Fields:

Go to System Definition > Tables.
Search and select the Incident table.
Add a new field with a type of 'Duration'.
Set the calculation to determine the difference between "Created On" and "Closed At" (or use Business Duration if business hours are crucial).
PA Widgets:

Go to Performance Analytics > Widgets.
Create a new widget with type 'Single Score'.
Configure the data source to pull from the Incident table, use your calculated fields, and set up your aggregations (like Average).
Interactive Filters:

Go to Performance Analytics > Interactive Filters.
Create a new filter, and under conditions, you can set up dependencies.
Always ensure you test thoroughly, especially when working with calculated fields and time-based metrics. The results can sometimes differ based on business hours, holidays, etc.

Hope that sets you on the right track! If you encounter any specific issues, please share them for more detailed assistance.

Best regards,
Ahmad

[SwarnadeepNandy · Accepted solution]

Hello,

To show the average difference between two fields created_on and closed_at, you can use the GlideAggregate class, which allows you to perform aggregation operations such as COUNT, SUM, MIN, MAX, and AVG. You can create a script that calculates the difference between the two fields for each record, and then use the addAggregate method to get the average of those differences. Here is an example script that you can modify according to your needs:

//Create a GlideAggregate object for the incident table
var agg = new GlideAggregate('incident');

//Add a query to filter the records by date range
agg.addQuery('sys_created_on', '>=', 'javascript:gs.daysAgoStart(30)');
agg.addQuery('sys_created_on', '<=', 'javascript&colon;gs.daysAgoEnd(0)');

//Add a scripted field that calculates the difference between created_on and closed_at in days
agg.addCalculatedField('floor((closed_at - created_on) / 86400000)');

//Add an aggregate function to get the average of the scripted field
agg.addAggregate('AVG', 'floor((closed_at - created_on) / 86400000)');

//Execute the query
agg.query();

//Get the result
if (agg.next()) {
  var avgDiff = agg.getAggregate('AVG', 'floor((closed_at - created_on) / 86400000)');
  gs.info('The average difference between created_on and closed_at is ' + avgDiff + ' days');
}

To create the other reports, you can use similar logic and methods, but with different fields and conditions. For example, to get the average incident lifespan, you can use the same scripted field as above, but without filtering by date range. To get the average time on new state, you can use a scripted field that calculates the difference between sys_created_on and sys_updated_on for records with state = new. To get the average time on work in progress state, you can use a scripted field that calculates the difference between sys_updated_on and closed_at for records with state = work in progress.

To create interactive filters for category and subcategory, you can use the Report Designer in ServiceNow. You can add a filter condition for category, and then add another filter condition for subcategory with a dependency on category. This way, when you select a category, only the relevant subcategories will be available for selection. You can also make your reports interactive by adding drilldowns, annotations, or actions.

Kind Regards,

Swarnadeep Nandy

[ujjwala_678]

Hello @SwarnadeepNandy 

Thank you for your input on this. Script is working for me.

I am little confused about other reports- avg time spent on new and wip state

Should I go with metrics and include scripts over there?

Any thoughts on this.

Thanks,

[SwarnadeepNandy]

Hello,

Yes, for this kind of logic where you need to track time spent on a particular state, go for Metrics.

Kind Regards,

Swarnadeep Nandy