Table of Contents
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
Setting | Description |
---|---|
New column | Enter the name of the column that will contain the aggregated values. |
Target column | Select the name of the column containing the values to be aggregated. |
Aggregation mode | Select 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 to | Select 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 groups | Check 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