Syntax
WINDOW_SUM(Indicator,[Grouping field 1,Grouping field 2,...],[Sorting field 1,Sorting method,Sorting field 2,Sorting method,...],[Window upper limit,Window lower limit])
This function is used to return the sum of the window expression.
WINDOW functions perform calculations based on the analysis views. In WINDOW functions, indicators, grouping fields, and sorting fields must all be of an aggregate nature, and dimension fields must all come from the analysis view.
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.
0 represents the current row.
-n represents the n row(s) before the current row.
n represents the n row(s) after the current row.
If the default value is used, data from the first row to the last row in the group is calculated (["first","last"]).
For details, see WINDOW Function Overview - Notes.
WINDOW_SUM(SUM_AGG(Sales),[City],[SUM_AGG(Sales)],[-2,0])
The total sales of the three consecutive rows in the City group are calculated.
WINDOW_SUM(SUM_AGG(Sales),[],[SUM_AGG(Sales)],["first",0])
The cumulative sales are calculated.
WINDOW_SUM(SUM_AGG(Sales),[Product],[Month],["first",0])
The monthly cumulative sales of different products are calculated.
For example, a grouping table has been created in the component, as shown in the following figure.
Data is grouped by Contract Payment Type and sorted by Total Amount in the ascending order in the analysis view to obtain the cumulative value within each group.
The formula to calculate Cumulative Amount Within Each Group of Contract Payment Type is WINDOW_SUM(SUM_AGG(Total Amount),[Contract Payment Type],[SUM_AGG(Total Amount),"asc"],["first",0])
In many cases, you need to calculate the offset value, such as the difference between the previous date and the next date or the conversion rate between the data in the previous step and that in the next step.
Taking the calculation of the conversion rate as an example, you need to obtain the value in the previous row to facilitate the division of the values in the two columns.
Sample data: Auto Industry Sales Funnel.xlsx
Analyze the number of customers in key nodes, and sort them in the descending order, as shown in the following figure.
Add a calculation field to calculate the number of customers in the previous row for each key node.
Number of Customers in the Previous Row = WINDOW_SUM(SUM_AGG(Customer Base),[],[SUM_AGG(Customer Base),"desc"],[-1,-1])
Drag Number of Customers in the Previous Row into the component, as shown in the following figure.
Finally, to calculate the conversion rate per node, use the formula SUM_AGG(Customer Base)/Number of Customers in the Previous Row, as shown in the following figure.
Drag Conversion Rate Per Node into the component analysis area to complete the analysis, as shown in the following figure.
For details, see Displaying the Sales of the Previous Three Months.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy