Performance analytics report to calculate duration between to fields

[robertmueller]

Hi,

Can someone advise the script needed in Performance Analytics to calculate the duration between two fields. For example I would like to calculate the duration between when the incident was opened and when it was assigned to someone.

Thank you in advance.

[robertmueller]

You have told me how to use the duration field on a metric value, you have not told me how to report the time difference between to time stamps without building a new field. Let me us another example as clearly it is confusing you. We have field in our systems created by support on our incident tickets, how would I calculate the difference between these two time stamps.

Alert reported - Which is a field the user manually enters the time and date

Corrective action determined - Again a manual time and date field.

Please do not again misunderstand my question and claim it is a knowledge gap I have as that is just rude and unhelpful.

[DJL2]

Hi Robert,

No problem at all, thank you for confirming you don't require my help.

Thanks,

DJL

[robertmueller]

Just to confirm I am looking for the formula to do formula indicators that calculate the time difference between to date stamps. The example I used was two date stamps on the metric table, but this could be two time taken from fields on an incident or a problem to calculate the difference.

I do not have access to create new fields on the tickets so it must be a formula indicator.

[Adam Stout · Accepted solution]

For time to assignment metrics, I prefer leveraging an SLA since this will allow me to easily calculate business time in addition to absolute time.  Reporting on SLAs is then pretty straight-forward.

If you don't go down this path, you need a Performance Analytics script to perform this calculation per row.  This cannot be done in a Formula since they operate on aggregates.

There should be examples available OOTB based on resolved time.  Just be sure to handle unassigned tasks as well (with score_start) or exclude them from your indicator.  This is the primary use case for Performance Analytics scripts and can also be found in the docs:  https://docs.servicenow.com/csh?version=latest&topicname=pa-scripts

[robertmueller]

Fantastic this is what I needed thank you, I needed to work through it a bit to get to what I needed but it is working.

The only thing is if in the future I did one from the metric table, for example the time between when the ticket was opened to when it was assigned to a 3rd line team, is there a way to only count the first entry, ie if it is assigned to a 3rd line team who assign it elsewhere and it comes back to them, is it possible to script so it only records the time between when it was first assigned and does not have a second entry for when it is assigned the second time?