Example 3: On average, how long does data stay invalid?

Problem: You want to understand how long data stays invalid, on average.

To create a PivotTable that indicates how long data stays invalid, on average

1. From the Problem Audit Summary in AVEVA Measurement Advisor, create a filter that gathers the following information:

   • Date

   • Problem Class — Data Validation

     For more information on filters, see Filters.

2. Export the Excel file.

3. Open the new file you created, and add a new column called Latency in Minutes and introduce the formula in the cell below:

   =MINUTE(IFERROR(TEXT(<Revision Date Time> - <Last Mod Time>,"hh:mm:ss.000"),0))

   Note: In order to be successful, you must replace the Column Name (in this case, the Revision Date Time and the Last Mod Time) with the corresponding cell location. For example, replace "<Revision Date Time> with the cell location (like S2) for the second row.

4. Create a PivotTable. For more information, see Create a PivotTable from the exported data.

5. Select the Problem Class and Problem Type from the Pivot Table Fields list, and add those to the Rows area.

6. Select Latency in Minutes and add it to the Values area.

7. Select on the Latency in Minutes field in the Values area and open the Value Field Settings.

8. Change the Summarize values field by to Average.

   

   When you are finished selecting the PivotTable fields, it should appear as follows:

   

   The resulting data would resemble the image below, with the the cell C5 being the average time the data remained invalid.