Window - script function
Window() performs calculations from multiple rows, producing a value for each row separately.
You can use the Window functions to perform operations such as:
-
Comparing an individual number value in a row with the average, maximum, or minimum within the column.
-
Calculating the rank of an individual value, either within the column or within the entire table.
The Window function does not change the number of records in the table but it can still perform similar tasks as aggregation, relational, and range functions.
The Window function must have a cache within the LOAD statement of the table you are working with to add to the table. For example:
Window supports general functions, such as rounding or basic numerical operations. For example:
You can define a sliding window for the Window function. This sets the number of rows used when applying the Window function on the current row. For example, you could set the window to be the 3 previous rows and the 3 subsequent rows.
Syntax:
Return data type: A new field added to the resulting table created by the LOAD statement.
Arguments:
Argument | Description |
---|---|
input_expr |
The input expression calculated and returned by the function. It must be any expression based on an aggregation, such as Median(Salary). For example: Window(Median(Salary)) as MedianSalary
The input can also be a field name with no aggregation applied. In this case, Window treats it like the Only() function is applied to that field. For example: Window(Salary,Department) as WSalary
Optionally, you can define partitioning with the input expression. Partitioning is the same as the grouping achieved by the group by clause, with the difference that the result is added as a new column to the input table. Partitioning does not reduce the number of records of the input table. Multiple partition fields can be defined. Example:
LOAD
Window(Max(Sales), City, 'ASC', OrderDate, Sales > 300)
+ AddMonths(OrderDate,-6) as MAX_Sales_City_Last_6_Mos, Window(Avg(Sales), City, 'ASC', OrderDate, City = 'Portland') + AddMonths(OrderDate,-6) as Avg_Sales_Portland_Last_6_Mos, Window(Max(Sales), City, 'ASC', OrderDate, Sales > 300) + AddMonths(OrderDate,-12) as MAX_Sales_City_Last_12_Mos; LOAD City, Sales, OrderDate FROM [lib://:DataFiles/Sales Data.xlsx] (ooxml, embedded labels, table is [Sales Data]);
|
partition1, partition2 |
After input_expr, you can define any number of partitions. Partitions are fields that define which combinations to apply the aggregations with. The aggregation is applied separately with each partition. For example: Window(Avg(Salary), Unit, Department, Country) as AvgSalary
In the above, the partitions are Unit, Department, and Country. Partitions are not mandatory, but are required for proper windowing of fields. |
sort_type, [sort_expr]] |
Optionally, specify the sort type and the sort expression. sort_type can have one of two values:
If you define sort_type, you need to define a sorting expression. This is an expression that decides the order of the rows within a partition. For example: Window(RecNo(), Department, 'ASC', Year)
In the above example, the results within the partition of sorted ascendingly by the Year field. Information noteThe sort type and sort expression are primarily only required with the RecNo and WRank functions.
|
filter_expr |
Optionally, add a filter expression. This is a Boolean expression that decides whether the record should be included in the calculation or not. This parameter can be omitted completely, and the result should be that there is no filter. For example: Window(avg(Salary), Department, 'ASC', Age, EmployeeID=3 Or EmployeeID=7)
as wAvgSalary) as wAvgSalaryIfEmpIs3or7 |
[start_expr,end_expr] |
Optionally, set the argument for sliding window functionality. A sliding window requires two arguments:
For example, if you wanted to include the 3 preceding rows, the current row, and the next following row: Window(concat(Text(Salary),'-'), Department, 'ASC', Age, Year>0, -3, 1) To indicate all preceding rows or all subsequent rows, you can use the Unbounded() function. For example, to include all preceding rows, the current row, and the following row: Window(concat(Text(Salary),'-'), Department, 'ASC', Age, Year>0, UNBOUNDED(), 1) For example, to include the third row from the current row and all subsequent rows: Window(concat(Text(Salary),'-'), Department, 'ASC', Age, Year>0, 3, UNBOUNDED()) |
Example - Adding a field containing an aggregation
Example - Adding a field containing an aggregation filtered for specific values
Example - Adding a field with a sliding window
Limitations
Window has the following limitations:
-
Window is a resource intensive function, particularly in terms of memory consumption.
-
Window is not supported in Qlik Sense Mobile.
-
Chart expressions do not support Window.
-
You cannot nest Window functions inside other Window functions.
-
Window cannot be used inside an aggregation function.
-
Window needs to be able to scan the whole table.
-
WRank(), RecNo(), and RowNo() cannot be used with Window when using the sliding window functionality.