9 Replies Latest reply on May 31, 2011 9:40 PM by dtakara

    Time Weighted Averages excluding selected time periods

    cjrancur

      I think it is likely that someone has already solved this problem, so I am asking for suggestions, either via Datalink, Excel VBA, vb.net or otherwise.

       

      It seems that it should be possible to use a PE filter in Datalink to exclude certain time periods, such as the time when a calibration test is occurring.  Can someone help me to write the filter to do this in Datalink?  I know I can exclude certain data values less than or greater than selected values, but a calibration test often runs up and down across the full range. Excluding certain values does work in this case, because there are not any specific values I want to exclude, only specific time periods that should be excluded.

       

      I could not see a way to do this using PE's in datalink, because the only time functions give an hour, day, month, or there are other time functions that show the amount of time that a certain value has been present or not in the archive for a given tag.  I'm hunting for a way to make a conditional statement that calculates time- weighted averages over a wide time period, with one or more sub-periods of time excluded.  I can't see how to say something like tag.timestamp < '21-May-2011 13:00:00.0000' and tag.timestamp > '21-May-2011 14:00:00.0000'.  Is there a way to do this for average calculated data in Datalink?

       

      I could program Excel VBA to do this, or I could write a separate application in vb.net or ACE, using either the SDK or PI OleDB. I want a general user to be able to get a result without too much trouble, so I think ACE is not appropriate, because a general user can't easily provide input times to an ACE calculation.

       

      Excel built-in statistical functions only calculate measurement weighted averages, as far as I can tell.  I need a time-weighted result.  I could write that function in VBA if necessary, but is it really necessary?  Have I overlooked an available Excel function?

       

      The Memorial day long weekend in the US is beginning shortly, depending on your time zone. For all of you sharing in this holiday, I hope that you will answer me after the weekend is over, unless this particular challenge would be recreation for you.  I wish you all a good weekend.

       

       Carrie

        • Re: Time Weighted Averages excluding selected time periods
          dtakara

          Carrie,

           

          How about this?

          1. obtain the start time and end time of "good" periods (i.e., when calibration is not being done)
          2. calculate the time-weighted totals of the "good" periods
          3. sum the lengths of the "good" periods
          4. sum the totals
          5. divide the result of step 4 by the result of step 3: this the time-weighted average you want

          The attached spreadsheet is the implementation of the algorithm above and involves no programming, but just using DataLink and native Excel function calls.

            • Re: Time Weighted Averages excluding selected time periods
              cjrancur

              For measurement weighted averages, I have seen mathematical proofs showing that the average of averages is not necessarily (and probably not usually) equal to the average from the raw data.  My hunch is that this same mathematical truism would apply to time weighted averages.  The last suggestion called for calculating an average of averages, so I am not comfortable with the validity of results obtained that way.  The EPA will receive the results in this case, so I want to take care to ensure that the average should be a good representation of the data.

                • Re: Time Weighted Averages excluding selected time periods

                  "...Average of an average is not equal to average from the raw data..."  - That really depends on how the average of average is being computed; i.e. if the weightage is being represented correctly to reflect the raw data when doing the "average of average."  But, I would not say in a blanket fashion that "average of average" is always incorrect.  

                  • Re: Time Weighted Averages excluding selected time periods
                    cjrancur

                    On second look, I see that the suggestion was not to calculate the average of averages, but instead to calculate the time weighted total, and then get the average from that.  I think that might be right, but I am still a little uneasy.  I wonder what formula OSIsoft uses to calculate time-weighted totals.

                      • Re: Time Weighted Averages excluding selected time periods
                        hanyong

                        From my understanding, time-weighted total is calculated as finding the time-integral of the tag values. And considering this and what Daniel suggested, finding the time-weighted total of the good period and then summing them up would be finding the shaded area of what you see in the figure below:

                         

                        0434.Figure1.JPG

                        • Re: Time Weighted Averages excluding selected time periods
                          dtakara

                          Maybe someone from the PI-SDK or PI Server Development Teams can confirm this, but I am pretty sure that the algorithm to compute time-weighted totals used by the PI-SDK and by the PI Performance Equations and PI Totalizer Subsystems in the PI Server is in accordance with the following:

                           

                          6378.Total3.png

                           

                           

                           

                          In simpler terms, the idea is to calculate the "area" between the lines linking the archived values and  the time axis. Let's take an example:

                           

                          0564.Total1.png

                           

                          1145.Total2.png

                            • Re: Time Weighted Averages excluding selected time periods
                              dtakara

                              Thanks for the input, Han Yong!

                               

                              Yes, a time-weighted total of tag values is basically the same as a time-integral of tag values.

                               

                              An attribute that affects how the time-weighted total is computed is STEP. The graphic in your post illustrates the case where step=ON, while the graphic in my post illustrates the case where step=OFF.

                                • Re: Time Weighted Averages excluding selected time periods
                                  cjrancur

                                  Thanks to all of you for the suggestions and explanations.  Daniel, you were right from the start.  The example you gave will do the job.

                                   

                                  Daniel described trapezoidal integration for tags with step=OFF, and Han described basic step integration for the case where step=ON. Those will work well for my application, which happens to have step=OFF.  In my case, the data tends to form a step pattern even though the PI tag has been configured with step=Off, because this data is sampled at discrete time intervals that are at a lower frequency than the PI scan rate.  In this data, there is some data transmission noise present in the signal.  Only the short times between PI interface scans, when the data is changing, will effectively get the trapezoidal integration method. Otherwise, trapezoidal and step integration will be essetially the same, except for small differences due to noise in the signal.

                                   

                                  Daniel's example will work for me. If the development team says that the actual implementation involves an algorithm that involves a repeated difference formula from a partial sum, that would also be OK because it should provide results that are equivalent to the equations described.