How to write formula to calculate Average of Business Elapsed Time?

[Peeyush Jaroli]

Dear Community Members,

I have a requirement where I need to calculate average of Business Elapsed time. I know through the list control we can see the total and average but business requirement is different than what is shown there.

What is shown:

Total: It shows total days/ hours of number of tickets

Average: It shows average of the tickets.

What is Needed:

The requirement is, It should look like sum(Business elapsed time per ticket)/count(tickets).

Below is example of what is needed:

1st ticket's sum is 15 Hours 29 Minutes

2nd ticket's sum is 12 Days 4 Hours 4 Minutes

The expected result should be: (15 Hours 29 Minutes+12 Days 4 Hours 4 Minutes) / 2

=6 Days 9 Hours 46 Minutes

Any way I can achieve this?

Regards,

Peeyush Jaroli

[Subrahmanyam2]

Hi Peeyush,

It is possible using the GlideAggregate API.
Please refer below link on how to use it. It has various examples on how to use it.
UNDERSTANDING GLIDEAGGREGATE

https://developer.servicenow.com/dev.do#!/reference/api/sandiego/server_legacy/c_GlideAggregateAPI#r...

Thanks and regards,

Subrahmanyam Satti

[Peeyush Jaroli]

Hi @subrahmanyam : Thanks for sharing this.

The aggregation used here are like SUM and AVG which I think are system default and will calculate how its been calculated in List Calculation.

Using SUM it shows = sum of all ticket's business elapsed time only

and Using AVG it shows = Average of all ticket's business elapsed time 

Could you please share any working script if you have tried, which I can check? Basically I need to calculate business elapsed time of task_sla table in as per below formula:

sum of business elapsed time/ no. of tickets.

Regards,

Peeyush Jaroli