Moving aggregation block

3 min

Use the moving aggregation block to calculate a measurement across a sliding or cumulative time window. Within this block, two types of aggregation are performed: regular and hopping. Regular aggregation applies a secondary aggregation over pre-aggregated data, similar to a window function in SQL, and does not change the dimensionality of the input. Hopping aggregation directly aggregates raw data and changes the dimensionality with no SQL equivalent.

Regular aggregation

Apply a secondary aggregation to the results of an existing aggregation over a specified time window. For example, calculate daily event counts (a regular aggregate) and then apply a trailing seven-day sum using a regular moving aggregation. Here is how to create a metric that calculates the sum of total ad revenue for the current day and the preceding six days.

Create a metric and add a Moving Aggregation block.

Select sum as the aggregator and Total Ad Revenue as the value in the Input section.

Choose Regular as the aggregation type under Configuration.

Set the points before the value to six and the points after zero.

Set the time granularity to Daily.

Run the visualization to see the results.

Hopping aggregation

Hopping aggregation computes metrics directly over a rolling time window without re-aggregating. For instance, if you have daily event counts, a hopping aggregation applies a seven-day trailing window that "hops" by one day at a time to calculate the same result. This method is essential for operations like rolling averages or distinct counts, which cannot be achieved with regular aggregation. The following creates an example metric to demonstrate a hopping aggregation:

Create a metric and add a Moving Aggregation block.

Select unique count as the aggregator and user_id as the value in the Input section.

Select Hopping as the moving aggregation type under Configuration.

Choose Trailing as the Window Type.

Configure the trailing window to cover the desired duration, 7 days.

Set the time granularity to Daily and run the visualization.

Configure the block with the following fields:

Metric selector – Choose from the following options:

Cumulative – Aggregate the metric in an expanding time window.

Rolling – Aggregate the metric over a sliding time window.

Aggregator – Select the function to aggregate input for a block:

avg – Compute the average of the input values.

count – Count the total number of input values, including duplicates.

unique count – Count the number of unique input values.

first – Compute the first input value, ordered by event time.

last – Compute the last input value, ordered by event time.

max – Compute the maximum input value.

min – Compute the minimum input value.

sum – Sum input values.

Input value – Choose the input value used to calculate the moving aggregation. This may be a dataset property, such as a column, derived column, or another block.

Time window – Varies depending on the metric selected in the metric selector.

Cumulative – Use a time window that is a time range over which to compute the cumulative measure.

Rolling – Specify the number of time points before and after.

Output

The output of the moving aggregation block is a numerical value.