Hi Everyone,

I am new to PI and it seems that I am out of my depth. I'm using Excel 2010 with the PI DataLink extension. What I am trying to do is find out for how long a vehicle was in idle, in hours, using a tag that we have set up that gives me engine RPM. In this case, any value less than or equal to 750 RPM but greater than 0 RPM is considered idle. In *addition* to this RPM range, five minutes needs to elapse before the event is considered to be an accumulation of idle time. This second part is what has me stumped, and nothing that I've found so far seems to do what I need it to.

So, to be clear, the idle time parameters are:

- engine RPM > 0 but <= 750 RPM
- above state needs to last more than (or equal to) five minutes to count as idle time
- I need to find the total amount of time where the above two statements are true in hours. (So idle time would be the five minutes it takes to qualify as an idle time event plus however long it went on for afterward).

I only have access to the DataLink side of things, so my hope is that there's a way to do this using the functionality offered by this tool. I apologize in advance for any misuses of terminology I may have used.

Any help you can provide would be greatly appreciated.

Dan

Hi Dan,

Firstly, welcome to the PI community!

As to your question, the most elegant solution to what you want to do would be to create a performance equation (PE) tag which has a value of 1 if the condition 0 < EngineRPM < 750 for the last five minutes and 0 otherwise (you can use the TimeGT and TimeLT functions to do this), then you can bring the values for this tag into excel using datalink and add up the time that this tag has a value 1 (and add 5 mins) to calculate you idle time.

Now you mentioned that you only have access to Datalink, and I have a solution for which should do what you want, although it is a little more complex. The idea is to use the performance equation functions TimeGT and TimeLT to calculate the number of seconds over the last 5 minutes that the 0 < EngineRPM < 750, then using standard excel functionality to calculate the amount of time this condition is true (plus the 5 mins lead time). The trick in getting this to work getting Datalink to accept the peformance equation function, this can be done using the procedure outline in the following KB article:

KB00745 - Using cell references for PE Calls used in PI DataLink

This requires a little bit of manipulation as the time stamps for the TimeGT and TimeLT functions have to be plain text not excel time stamps.

I have attached a spreadsheet to this message which you can use as a basis for this calculation. It calculates the number of minutes over a 30 minute period that the tag 'sinusoidu' is in the range 94 to 97, and has been in that condition for at least 5 mins. The key formula to calculate this is the following:

=PIExpVal("TimeGT('sinusoidu','"&D21&"','"&D26&"',94.0)","*",0,"MyPIServer")+PIExpVal("TimeLT('sinusoidu','"&D21&"','"&D26&"',97.0)","*",0,"MyPIServer")

Where cells D21 and D26 refer to 5 minutes ago and the present respectively. This equation will be equal to 600 only if the above conditions are true at the time in cell D26. You can then use the output of this equation to calculate the total time that this equation is true using standard excel functions (such as countif()). Below is a screenshot of what my spreadsheet looks like.

I hope that this helps you achieve what you're trying to do.

Cheers,

Michael