How to create filtered averages, totals, and rollups
Calculations like an average of a set of data, but only when the values are above a certain threshold cannot be done directly with the built in functions in Analytics. The examples below show how you can perform filtered calculations for Expressions and for Rollups. (There is a Knowledge Based article on this subject, KB01120 )
Suppose you want to get an average flow rate for a pump over a time range, but only when the pump was pumping.
In this example, the time weighted and event weighted averages are calculated for a one day period. The flowrate during this time period is 0 on several occasions. Since we want to know the average flowrate of the pump only when it is running (flowrate > 0), then using the built in TagAvg() and TagMean() functions will give us the wrong results. The Figures below show the data set, the Expressions and the wrong results.
Filtered Time-Weighted Average
To get the correct time-weighted average we need to first create another Attribute, Filtered Flowrate in this example, and use the Formula Data Reference as shown in the Figure below.
Then create an expression that first uses the TimeGT() function to calculate the amount of time that the Flowrate Attribute was greater than 0 over the one day period. This result is in seconds; therefore, we need to convert it into days, which is the PumpRunningInDays Variable. Then TagTot() function is then applied to the Filtered Flowrate Attribute to sum the values
over the one day period. The TagTot() returns the total value over a day, so we then divide this by the PumpRunningInDays Variable.
Filtered Event-Weighted Average
To get the correct event-weighted average we need to create another Attribute and a PI Point. Use an Expression to filter out the values that are 0 and only write these to a new Attribute.
Then use the built in function TagMean() to compute the event-weighted average on this new Attribute.
We want to perform a rollup of data from child-elements, but only include the data where some other conditions are met, in other words a conditional rollup. The Rollup Analytic can perform the aggregations from child-elements, but cannot filter out unwanted values. To achieve this we need to follow the procedure presented in the example below.
Suppose we want to get the total production for all gas wells in a region, but not count flowrates that are below 25 MM scf/d. Therefore, for the wells we create a new Attribute, Filtered Production, mapped to a PI tag. The Expression for the filtering is shown in the Figure below.
The wells are child-elements of the Well Padd1 element.
The Well Padd1 element then has a Rollup Analytic on the Filtered Production Attribute.