Overview
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]) |
|
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
| Formula | Result | Note |
|---|---|---|
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])
Conversion Rate Comparison Between the Current and Previous Nodes
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
Analyzing the Number of Customers in Each Node
Analyze the number of customers in key nodes, and sort them in the descending order, as shown in the following figure.
Calculating the Value in the Previous Row
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.
Calculating the Conversion Rate
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.
Displaying the Sales of the Previous Three Months
For details, see Displaying the Sales of the Previous Three Months.
