User Tools

Site Tools


transformations:slidingaggregation

SLIDING AGGREGATION

Category: Transform / Advanced


Description

This action calculates an aggregate, such as average, of N sliding values. Available aggregations are: Average, Sum, Min, Max, Count Distinct.


Use cases

This action can be used for adding smoothed trendlines to charts.


Action settings

SettingDescription
New columnEnter the name of the column that will contain the aggregated values.
Target columnSelect the name of the column containing the values to be aggregated.
Aggregation modeSelect the aggregation method to be used on the values in the Target column. Options: Average, Sum, Min, Max, or Count Distinct.
Window size*Enter the number of consecutive rows to be included in the calculation.
Offset toSelect whether the calculated values in the New column will align with the top ("start") of the window, or the bottom ("end"). Options: Window start or Window end.
In groupsCheck this "on" and select one or more columns to group the aggregated results in.

* Setting can be specified using a parameter.


Remarks

The Window size determines how many rows' values are included within each calculation. A Window size of "4" includes 4 consecutive rows. The Offset to selection determines whether the output value aligns with the top/start or bottom/end of the current window.

If there aren't enough rows within the dataset or a group (if grouping is used) the Window size will be set to the number of available rows and a single calculation will be generated for that window.


Examples

Example #1

Create sliding aggregations (sums) for the column "value".

Before (source table)

product sale date value
GTX Plus 6/8/2018 900
GTX Plus 6/24/2018 1000
MG Special 6/22/2018 60
MG Special 6/27/2018 70

After (result table)

product sale date value agg_sum
GTX Plus 6/8/2018 900 1900
GTX Plus 6/24/2018 1000 1060
MG Special 6/22/2018 60 130
MG Special 6/27/2018 70

Action parameters

New column: agg_sum
Target column: value
Aggregation mode: Sum
Window size: 2
Offset to: Window start


Example #2 Illustrated



See also

transformations/slidingaggregation.txt · Last modified: 2025/01/28 10:52 by yurii

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki