4 Replies Latest reply on Jun 24, 2017 7:22 AM by Jim Dehner

# Calculate Daily KPIs

All,

I have Daily outage metrics is the following form

 Category Date EventID Target Value Mean Time to Restore 2-Jun-17 1 60 0 Mean Time to Restore 3-Jun-17 1 60 0 Mean Time to Restore 4-Jun-17 1 60 0 Mean Time to Restore 5-Jun-17 1 60 0 Mean Time to Restore 6-Jun-17 1 60 0 Mean Time to Restore 7-Jun-17 1 60 0 Mean Time to Restore 8-Jun-17 1 60 0 Mean Time to Restore 9-Jun-17 1 60 0 Mean Time to Restore 10-Jun-17 1 60 0 Mean Time to Restore 11-Jun-17 1 60 0 Mean Time to Restore 12-Jun-17 1 60 0 Mean Time to Restore 13-Jun-17 1 60 0 Mean Time to Restore 14-Jun-17 1 60 0 Mean Time to Restore 15-Jun-17 1 60 0 Mean Time to Restore 16-Jun-17 1 60 0 Mean Time to Restore 17-Jun-17 1 60 0 Mean Time to Restore 18-Jun-17 1 60 25 Mean Time to Restore 18-Jun-17 2 60 67 Mean Time to Restore 18-Jun-17 3 60 123 Mean Time to Restore 19-Jun-17 1 60 0 Mean Time to Restore 20-Jun-17 1 60 0 Mean Time to Restore 21-Jun-17 1 60 0

Description of the data

1. Everyday we track outages. There could be multiple outage in a single day.

2. The outage goal is marked "Met" if the duration of the outage is less than the Target, else marked "Failed" (I was able to accomplish this easily....see attached workbook)

Problem

1. I need to calculate a value for every row Called "Daily KPI" which should be "Failed" if any one outage event in a day failed to been the Target.

Example (For 18-Jun-17 in the above data)

a. There were 3 outages

b. 1 outage was 25 mins which was below the Target

c. 2 outages were above Target

d. So for 18-Jun-17 the Daily KPI should be "Failed" for all 3 rows

Is this possible. If yes, please give me some ideas.

Attached is the workbook. The first sheet displays the outages that occurred over the year and the colors indicate the unique outage in a particular day. The second sheet is tables which displays Met Goal? based on each outage. I need another field which displays Daily KPI which should be either "Met" or "Failed" depending on the number of outages that were above the Target for that day.

• ###### 1. Re: Calculate Daily KPIs

Hi

There are a series of calculated fields

Failed = if ([Met Goal ?])='Failed' then 1 else 0 end

Daily count of failed = { FIXED [Date]: sum([Failed])}

Count of failed days = if sum([daily count])>0 then COUNTD([Date]) end

Let me know if this helps

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Calculate Daily KPIs

Thanks for calculations.

Is it possible to get the year to date Failed Days count.

when I remove the Date from the view the Count of Failed > 0 returns 172. I am looking for the number of days that failed which should be 7.

Any help would be greatly appreciated.

• ###### 3. Re: Calculate Daily KPIs

I think I got the answer.