Time Weighted Averages excluding selected time periods

Discussion created by cjrancur on May 27, 2011
Latest reply on May 31, 2011 by dtakara

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.