Calculate duration of non-overlapping time

Question

I am calculating service availability for a month. For that I have outage start time and outage end time. I want to know out of total time in minutes for a month, how much percentage of service was disrupted.

E.g Service Availability = Total time - Total_outage_time / Total time.

However, I am not able to exclude the overlapping outage time. I do not want to calculate the overlapping duration. Can someone help?

OutagesStart timeEnd timeOutage 12019-11-04 13:04:00 2019-11-05 16:57:34Outage 22019-10-05 11:20:00  2019-10-05 02:18:03Outage 32019-10-20 16:19:002019-10-21 02:18:29Outage 42019-10-30 02:29:002019-10-31 06:46:56

OllieClarke · Accepted Answer

Hi @AnujaJ is this what you're after?

This workflow calculates the % of minutes per month where there wasn't any outage.

Hope that helps, if so please could you mark it as a solution to help the community 🙂

Ollie

OllieClarke · Accepted Answer

@AnujaJ Sure!

Firstly I generate every minute between the start time and end time for each outage using the generate rows tool:

I then find the month for each minute of outage.

Using this month I count how many distinct minutes of outage there are in each month.

I then calculate how many minutes there are in each month. To do this I add 1 month to the month, then minus 1 minute, then count the minutes between the month and this value.

Finally I can use your formula to calculate the service availability.

Does that make things clearer?

Ollie