### 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 )

## Expressions

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.

## Rollups

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.

Ales Soudek Thanks for the great write-up. I initially thought Data Link's filter expressions should be a piece of cake with analytics but it is proving to be a challenge.

While the pump flow condition is a good example, in our case - we want to calculate an average of Tag D based on whenever Tag A, B, C are true. In other words :

a rolling daily/monthly/yearly average of Tag A for those time stamps when Tag A, B, C > 200 for instance. I am simplifying our requirement. We have 8 tags that should satisfy the condition before we calculate a time-weighted average of the 9th Tag for the time-stamps for the satisfied conditions.

Here is our Formula reference :

[if (A > 218.5 and B > 218.5 and C > 218.5 and D > 218.5 and E > 218.5 and F > 218.5 and G >= 99.5 and H >= 99.5) then J else 0]

'J' being the tag we are using to calculate the Average. This sounds more of an event frame where we want to find all the time stamps when the condition is true before we calculate the average for time stamps. Stephen Kwan