Overview
WINDOW functions are commonly used for cross-row calculation, that is, calculation on data within the window range (upper/lower limit) after data is grouped and sorted.
WINDOW functions can output fields separately or be used together with DEF functions. When calculating fields across rows in the analysis view, WINDOW functions are simpler, easier to understand, and more performant than EARLIER functions.
Common Scenario
WINDOW functions perform aggregation based on the analysis view, namely, perform calculation on the generated component results (excluding detail tables). They are commonly used in the following scenarios:
Accumulation
In-group accumulation
Moving average within three consecutive rows
Moving average within three consecutive rows in one group
Nesting Scenario
When WINDOW and DEF class functions are nested, data is calculated based on the independent view of the DEF function. In this case, if the DEF(Specified dimension) function is used, detailed-level fields can be output.
In scenarios where DEF and WINDOW functions are nested, WINDOW functions can be used in self-service datasets and detail tables.
Where to Use
| Item | Where to Use |
|---|---|
Self-service dataset | |
Component |
Note:Function Description
View Concept
First, the following will introduce the concept of views. Two views (detail view and analysis view) are available in FineBI.
Detail view: data editing page and component page where Chart Type is set to Detail Table, on which data is calculated at the detail level.
Analysis view: visualization component editing page, on which data is summarized, aggregated, and visualized.
WINDOW functions perform calculation based on the analysis views. In WINDOW functions, indicators/grouping fields/sorting fields must all be of an aggregation nature, and dimension fields must all come from the analysis view.
Function Syntax
WINDOW function expression:
WINDOW_Summary Method(Indicator,[Grouping field 1,Grouping field 2,...],[Sorting field 1,Sorting method,Sorting field 2,Sorting method,...],[Window upper limit,Window lower limit])
| Parameter | Description |
|---|---|
Indicator | The value cannot be empty. |
Grouping field | If the default value is used, data is not grouped by any field. |
Sorting field | If the default value is used, data is calculated in the default order. |
Sorting method | The value can be "asc" (ascending order) or "desc" (descending order). If one sorting method uses the default value, all sorting methods need to use the default value. If the default value is used, data is calculated according to the sorting field in the ascending order. |
Window upper/lower limit | The value can be "first" (first row in the group) or "last" (last row in the group). The value can be numerical constants. The option includes 0 (current row), -n (n row(s) above the current row), and n (n row(s) below the current row). If the default value is used, data from the first row to the last row in the group is calculated (same effect as ["first","last"]). |
WINDOW functions vary according to used summary methods (including summary, average, maximum, minimum, standard deviation, and variance).
In addition, RANK_AGG is also one WINDOW function used to sort grouped fields by specified rules.
Simple Example
You can download the sample data: Supermarket Sales Data.xlsx.
1. Create a group table and drag in Order Date and Sales to calculate the sales in different years and months.
2. Calculate the moving average of sales in the past three months, as shown in the following figure.
The formula is WINDOW_AVG(SUM_AGG(Sales),[],[Order Date],[-2,0]).
| Formula Content | Description | Remarks |
|---|---|---|
WINDOW_AVG(SUM_AGG(Sales)) | The average sales are calculated across rows. | WINDOW_AVG |
Parameter 2: [] | Data is not grouped. | |
Parameter 3: Order Date | Data is sorted by order date (year-month) in the ascending order.
| If no sorting method is specified, data is sorted in the ascending order by default. |
Parameter 4: [-2,0] | Data within the window range is retrieved.
| Data from the two preceding rows to the current row is retrieved in the sorting order by order date (year-month), based on which the moving average is calculated. |
After completion, drag fields into the group table and set Quantity Unit to Thousand in Value Format.
Function List
| Function | Scenario |
|---|---|
| WINDOW_SUM | Conversion Rate Comparison Between the Current Node and the Previous Node |
WINDOW_AVG | Calculating the Moving Average |
WINDOW_MAX | / |
WINDOW_MIN | / |
WINDOW_STDEV | / |
WINDOW_VAR | / |
RANK_AGG | Horizontal Ranking in a Cross Table |
Notes
Additional Description
WINDOW functions perform calculation based on the analysis views. In WINDOW functions, indicators/grouping fields/sorting fields must all be of an aggregation nature, and dimension fields must all come from the analysis view.
WINDOW functions directly output aggregation fields, which is not supported in self-service datasets. In this case, WINDOW functions need to be used together with DEF functions to output detailed-level fields.
If you have a higher requirement on sorting, you are advised to list both sorting fields and sorting methods in sequence into the WINDOW function. If some fields are not specified, data is calculated in the default order.
Year/Month Accumulation Scenario
Fields in the to-be-analyzed area are first used in the current WINDOW calculation and then dragged into the analysis area. The analysis area also allows you to group data by date and by custom dimension. When multiple grouping methods are set for a field repeatedly dragged into the analysis area, all groupings set for the field will take effect in the WINDOW calculation.
For example, Order Date is grouped by Year after dragged into the analysis area for the first time, and grouped by Month after dragged into the analysis area again. In this case, Order Date in the WINDOW function is one grouped by year and month.
If you want to calculate the monthly cumulative sales by year, copy the Order Date field in the to-be-analyzed area and rename the pasted fields to differentiate different groups.
Drag the fields Year and Month into the analysis area and group them in sequence. Add a calculation field named In-Group Cumulative Sales to calculate the monthly cumulative sales by year.
The following figure shows the cumulative sales by month in 2013.
