Calculating an Average

There are actually a couple of different ways to capture average, min or max values, depending on if you want to capture all of the values in your table, or just a couple.

Capturing All Values

To capture the average value of all of the columns in a table, you can use the Flatten layer.

This captures a key aggregate (such as first, last, min, max or avg) value from a table and writes it to your capture attributes.

Note that unless you set the value of 'retain' to true, this also removes any table or timeframe data the layer contains.

See the following example…

Assuming we have a table (probably loaded with Get Query) containing the following…

Temperature	Speed	Current	Product
10	211	5.1	1
10	210	5.2	1
20	209	5.1	2
20	210	5.2	2

…along with the configuration below…

{
   "type": "flatten"
   "method": "avg"
}

Your final capture would contain something like the results below…

Temperature: 15
Speed: 210
Current: 5.15
Product: 1.5.

Capturing Specific Values

If you're looking for something specific or you'd like to control what each point is called, you can use the ValueSummary layer.

In this case, we list the names of the columns we want to average. We can also provide conditions.

You can use the following…

{
   "columns": ["Temperature"],
   "named": ["Average Water Temp"],
   "methods": ["avg"]
}

Your final capture would contain…

Average Water Temp: 15.

Splitting By Other Values

The ValueSummary method can also include conditions and be split. For example, if we wanted to know the average temperature per product, we could use…

{
   "columns": ["Temperature"],
   "prefix": "Average ",
   "named": " Water Temperature"
   "methods": ["avg"],
   "splitby": "Product"
}

Your final capture would contain…

Average 1 Water Temp: 10
Average 2 Water Temp: 20

Calculating an Average