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 ranked.
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.
Self-service dataset
Adding a Formula Column
Component
Adding a Calculation Field
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/ranking fields must all be of an aggregation nature, and dimension fields must all come from the analysis view.
WINDOW function expression:
WINDOW_Summary Method(Indicator,[Grouping field 1,Grouping field 2,...],[Ranking field 1,Ranking method,Ranking field 2,Ranking method,...],[Window upper limit,Window lower limit])
Indicator
The value cannot be empty.
Grouping field
If the default value is used, data is not grouped by any field.
Ranking field
If the default value is used, data is calculated in the default order.
Ranking method
The value can be "asc" (ascending order) or "desc" (descending order). If one ranking method uses the default value, all ranking methods need to use the default value. If the default value is used, data is calculated according to the ranking 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 rank grouped fields by specified rules.
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]).
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 ranked by order date (year-month) in the ascending order.
Ranking field: Order Date
Ranking method: No ranking method is specified. Therefore, data is ranked in the ascending order by default.
If no ranking method is specified, data is ranked in the ascending order by default.
Parameter 4: [-2,0]
Data within the window range is retrieved.
Window upper limit: -2 (Data from the first two rows is retrieved according to the ranking order.)
Window lower limit: 0 (Data to the current row is retrieved.)
Data from the two preceding rows to the current row is retrieved in the ranking 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.
Conversion Rate Comparison Between the Current Node and the Previous Node
Displaying the Sales of the Previous Three Months
Calculating the Moving Average
WINDOW_MAX
/
WINDOW_MIN
WINDOW_STDEV
WINDOW_VAR
RANK_AGG
Dense Ranking
Horizontal Ranking in a Cross Table
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 ranking, you are advised to list both ranking fields and ranking methods in sequence into the WINDOW function. If some fields are not specified, data is calculated in the default order.
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.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy