Skip to main content Skip to complementary content

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:

[Transactions]: Load *, Window(avg(Expression1),[Num]); LOAD TransLineID, TransID, "Num", Dim1, Dim2, Dim3, Expression1, Expression2, Expression3 FROM [lib://DataFiles/transactions.qvd] (qvd);

Window supports general functions, such as rounding or basic numerical operations. For example:

Load *, Round(Window(Sum(Salary),Department)) as SumSalary
Load *, Window(Sum(Salary),Department) + 5 as SumSalary

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:  

Window (input_expr, [partition1, partition2, ...], [sort_type, [sort_expr]],[filter_expr], [start_expr,end_expr])

Return data type: A new field added to the resulting table created by the LOAD statement.

Arguments:  

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:

  • ASC: Ascending sorting.

  • DESC: Descending sort.

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:

  • Start expression: The number of rows prior to the current row to include in the window.

  • End expression: The number of rows after the current row to include in the window.

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)
as WSalaryDepartment

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)
as WSlidingSalaryDepartment

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())
as WSlidingSalaryDepartment

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!