Overview
Syntax | WINDOW_AVG(Indicator,[Grouping field 1,Grouping field 2,...],[Sorting field 1,Sorting method,Sorting field 2,Sorting method,...],[Window upper limit,Window lower limit]) |
|
Parameter 1 | Indicator | The value cannot be empty. |
Parameter 2 | Grouping field | If the default value is used, data is not grouped by any field. |
Parameter 3 | Sorting field | If the default value is used, data is calculated in the default order. |
Parameter 4 | Sorting method | The value can be asc (ascending order) or desc (descending order). If one sorting method is defaulted, all sorting methods need to be defaulted. In this case, data is sorted by the sorting field in the ascending order. |
Parameter 5 | Window upper/lower limit | The value can be "first" (first row in the group) or "last" (last row in the group). Numeric constants are supported.
If the default value is used, data from the first row to the last row in the group is calculated (["first","last"]). |
Notes
For details, see WINDOW Function Overview - Notes.
Example
1. Simple Example
| Formula | Result | Note |
|---|---|---|
WINDOW_AVG(SUM_AGG(Sales),[City],[SUM_AGG(Sales)],[-2,0]) | The average sales of three consecutive rows in the City group are calculated. | |
WINDOW_AVG(SUM_AGG(Sales),[],[Order Date],[-1,-1]) | The sales of the last month are calculated. | |
WINDOW_AVG(SUM_AGG(Sales),[],[Order Date],[-2,0]) | The average sales in the past three months are calculated. | |
WINDOW_AVG(SUM_AGG(Sales),[],[Region,Year],[Month],[-2,0]) | The average sales in the past three months of the region is calculated. To ensure data accuracy, the source fields for the year and month are different. |
2. More examples: Moving Average
Calculate the moving average in the past three months.
