7 Replies Latest reply on Jun 21, 2017 4:25 AM by skwan

    AF Filtered Calculations


      Hi everyone,


      I need to calculate daily average truck speeds with certain filters in place.  Doing this in Excel using PI Datalink is easy, however doing the same in AF Analysis is a bit more tricky.  I've used this guide to setup my calculations:

      Request Rejected


      My problem is that I don't get the same answer using PI Datalink and using AF Analysis:


      • PI Datalink: 17.307
      • AF Analysis: 16.727


      See below for how calculations was setup.  Any idea what I'm doing wrong?


      DataLink Calculations

      Data Item: Truck.Speed
      Filter: ('Truck.Status'=228) AND ('Truck.Speed'>0) AND ('Truck.NetworkProblem'=0)

      From: '*-1D'

      To: '*'

      Calculation Mode: average (time-weighted)


      Answer: 17.307


      AF Calculation

      First I create a formula data reference attribute (Filtered Speed) with the following formula:

      A=Speed;B=NetworkProblem;C=Status;[if A > 0 and B == 0 and C == 228 then A else 0]



      Then I create an Analysis:



      Answer: 16.727

        • Re: AF Filtered Calculations
          John Messinger

          Hi Franco,


          The first thought that comes to mind here is that as you are using a moving relative time period ('*-1d' to '*'), do you still get different results if you use a fixed time period in both DataLink and AF:


          StartTime = 'y'

          EndTime = 't'


          The difference you noted between the two results is small enough that it could be accounted for by the different relative times resolved by both methods. Using a fixed (non-moving) time range would at least allow you to eliminate the possibility that the difference is not due to two slightly different time ranges being used in your calculations.

          • Re: AF Filtered Calculations
            Rick Davin

            I worked a similar case for ExpressionSummaries once.  The problem there was that for DataLink, if there are no recorded values at the start and/or end times, then DataLink uses interpolated values for the respective start and end time.  Thus DataLink might be adding 2 extra values into its calculations.


            Also there will be a slight error in your calculations.  Your StartTime is defined as '*-1d' and later you divide by 86400, which is the number of seconds in a standard 24-hour day.  For relative time strings during DST transition, '1d' might be 23 or 25 hours depending upon which way the transition goes.  There are 2 safer ways to do this.  One would be to be to change the StartTime expression to '1-24h'.  The other way is not to hardcode 86400 but have it be calculated on what a "day" is: Float(EndTime - StartTime).


            See https://pisquare.osisoft.com/message/70358-re-af-2015-operations-dates#comment-70358 for more.

            1 of 1 people found this helpful
              • Re: AF Filtered Calculations

                Thanks.  The values being used are in the tens of thousands for a day so I don't think two points will make much of a difference.  Also We don't have daylight savings here, but I'll change it anyways for better code. 

                  • Re: AF Filtered Calculations


                    Not sure if you contacted tech support for this.  In case you have not, try this.  I would start by making sure that your Formula DR is producing the same results as the DataLink filtering function.  In PI System Explorer, create PI Point DR attributes for your Truck.Status, Truck.Speed and Truck.NetworkProblem configured with the appropriate PI Point.  Now, in PSE, you can right-click on an attribute and choose the Time Series Data dialog box.  In the resultant dialog box, you can apply filters, just like you would in DataLink.  See example here.  The filtering only works for PI Points.



                    Now create a Formula DR as you have shown above and in a similar fashion, get your list of values from the Time Series Data dialog box.  In theory, the Formula DR should produce the same list of values as the previous step.  Go from here to continue your debugging and testing.

                    As usual, you are welcome to contact tech support and they can help you if you rather not do this yourself.


                    Steve Kwan