Total amount of time - AF/PowerPivot

Discussion created by izaias on Mar 14, 2013
Latest reply on May 21, 2013 by mhamel

We are developing an asset model using PI-AF wich will provide information to a report solution based on PowerPivot and Dynamic Tables (MS Excel).
In this scenario, we would like to compute the total amount of time a digital PI point was kept on a specific state.
On PowerPivot, we currently have the following information (extracted from PI-AF through PI SQL Commander):

Timestamp                          Status Value
01/01/2013 00:00:00           ON
01/01/2013 00:20:00           OFF
01/01/2013 00:25:00           ON
01/01/2013 00:30:00           OFF
01/01/2013 00:40:00           ON

From that, we need to obtain the total amount of time a point was kept on specific state.
As an example, the resulting calculation for the above case would be:
- ON: 25 min
- OFF: 15 min

We assume this is a rather common problem but we couldn't figure a solution out. We are not sure whether the calculation should be performed by PI-AF and more importantly, how to do it. Can anyone help on providing a way to carry such computation?

Thanks in advance.