Need to Calculate uptime in Pivot

[neelesh4]

Hi All,

I have created a report and added a pivot to obtain the total downtime of a service # in a month and the up time of the service on monthly basis.

I tried to add a field on pivot using =1-('TL DOWNTIME' /DAYS) to calculate the up time. but the result are incorrect.

the raw data is in "Tickets Data" sheet.

in which, I added the inventory of service. and added the ticket data. to differentiate inventory from Ticket data have following column header
Tickets. Major Tickets, Circuit Count, DAYS.

but the uptime % in Uptime By Service ID sheet is not coming accurate.

I want to calculate uptime on month on month basis and Fix the formula in the field on pivot =1-('TL DOWNTIME' /DAYS)

[JeteMc]

Try modifying the field1 formula to read: =1-('TL DOWNTIME'/(MIN(DAYS)*8))
Note that I put in the value 8 to represent the hours in a workday, it should be changed to represent the actual number of hours that the service #'s are used per day.
I notice that E47 is highlighted with a value of 99.716%. I assume that is not correct so if the above mentioned change doesn't help, please tell us what the correct value should be and unless blatantly obvious how it would be manually calculated.
Let us know if you have any questions.

[neelesh4]

Hi JeteMc

thank you for your response. the service is 24/7 o I replaced 8 by 24 and the new result is 99.988% which is also not correct.
the correct result that is obtained manually is 99.148%.

formula =1-(downtime/number of days in the respective month)

I did some research over the weekend, the formula is calculating the number of days multiple times. in case of the service ID A40. it is taking February month days (29) 3 times from "ticket Data Sheet" i.e. from Source Type
Inventory
Email
Fault Management

I need to find a way that the formula only pick the number of days in respective month only once.

[JeteMc]

I feel that the way to do what you want is to add a Min of DAYS column to the pivot table and use a column outside of the pivot table populated using:

Please Login or Register  to view this content.

Let us know if you have any questions.

[neelesh4]

Thank you JetMc this works perfectly outside pivot table. when I try to add this formula in calculate field, it give me an error. Reference, name, and arrays are not supported in pivot table formulas.

Is it this picking up data from the Tickets Data Sheet Days Column "AE"?

[JeteMc]

The GETPIVOTDATA formula is meant to be used outside of the pivot table, not as a calculated field.
According to support.office.com "The GETPIVOTDATA function returns visible data from a PivotTable".
Let us know if you have any questions.

[neelesh4]

right but this cause an issue when I enter another month to the table.

with the above solution, i would have to add an additional row outside pivot table for calculation. I am trying to achieve a formula that can be entered in "field" function to pick number of days only once.

[JeteMc]

Would it be possible to only enter 29 as the number of days for the rows that have inventory in column A? When I delete the 29's from cells AE70:AE146 then I get the value of 99.148% for cell E47 in the file attached to post #1.
Column AE (Tickets Data sheet) could be populated using: =IF(A2<>"Inventory","",DAY(EOMONTH(B2,0)))
Let us know if you have any questions.

[neelesh4]

Thanks Jete and sorry for the delay in response. I marked /30 in the formula and that helped

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.