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

Load script

Create a new tab in the data load editor, and then load the following data as an inline load. Create the table below in Qlik Sense to see the results.

Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id) as AvgCustTransaction;

Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, M, Orange
3752, 20180916, 5.75, 1, 5646471, S, Blue
3753, 20180922, 125.00, 7, 3036491, L, Black
3754, 20180922, 484.21, 13, 049681, XS, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
3758, 20180924, 153.42, 14, 2038593, L, Red
3759, 20180925, 7.42, 5, 203521, M, Orange
3760, 20180925, 80.12, 18, 5646471, M, Blue
3761, 20180926, 3.42, 7, 3036491, XS, Black
3763, 20180926, 63.55, 12, 049681, S, Red
3763, 20180927, 177.56, 10, 2038593, L, Blue
3764, 20180927, 325.95, 8, 203521, XL, Black
];

Results

Results for adding a field containing an aggregation
transaction_id	transaction_date	transaction_amount	transaction_quantity	customer_id	size	color_code	AvgCustTransaction
3750	20180830	23.56	2	2038593	L	Red	103.43
3751	20180907	556.31	6	203521	M	Orange	266.775
3752	20180916	5.75	1	5646471	S	Blue	42.935
3753	20180922	125.00	7	3036491	L	Black	64.21
3754	20180922	484.21	13	049681	XS	Red	273.88
3756	20180922	59.18	2	2038593	M	Blue	103.43
3757	20180923	177.42	21	203521	XL	Black	266.775
3758	20180924	153.42	14	2038593	L	Red	103.43
3759	20180925	7.42	5	203521	M	Orange	266.775
3760	20180925	80.12	18	5646471	M	Blue	42.935
3761	20180926	3.42	7	3036491	XS	Black	64.21
3763	20180926	63.55	12	049681	S	Red	273.88
3763	20180927	177.56	10	2038593	L	Blue	103.43
3764	20180927	325.95	8	203521	XL	Black	266.775

Example - Adding a field containing an aggregation filtered for specific values

Load script

Create a new tab in the data load editor, and then load the following data as an inline load. Create the table below in Qlik Sense to see the results.

Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id, color_code = 'Blue') as AvgCustTransaction;

Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, M, Orange
3752, 20180916, 5.75, 1, 5646471, S, Blue
3753, 20180922, 125.00, 7, 3036491, L, Black
3754, 20180922, 484.21, 13, 049681, XS, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
3758, 20180924, 153.42, 14, 2038593, L, Red
3759, 20180925, 7.42, 5, 203521, M, Orange
3760, 20180925, 80.12, 18, 5646471, M, Blue
3761, 20180926, 3.42, 7, 3036491, XS, Black
3763, 20180926, 63.55, 12, 049681, S, Red
3763, 20180927, 177.56, 10, 2038593, L, Blue
3764, 20180927, 325.95, 8, 203521, XL, Black
];

Results

Results for adding ad field containing an aggregation filtered for specific values
transaction_id	transaction_date	transaction_amount	transaction_quantity	customer_id	size	color_code	AvgCustTransaction
3750	20180830	23.56	2	2038593	L	Red	-
3751	20180907	556.31	6	203521	M	Orange	-
3752	20180916	5.75	1	5646471	S	Blue	42.94
3753	20180922	125.00	7	3036491	L	Black	-
3754	20180922	484.21	13	049681	XS	Red	-
3756	20180922	59.18	2	2038593	M	Blue	118.4
3757	20180923	177.42	21	203521	XL	Black	-
3758	20180924	153.42	14	2038593	L	Red	-
3759	20180925	7.42	5	203521	M	Orange	-
3760	20180925	80.12	18	5646471	M	Blue	42.94
3761	20180926	3.42	7	3036491	XS	Black	-
3763	20180926	63.55	12	049681	S	Red	-
3763	20180927	177.56	10	2038593	L	Blue	118.4
3764	20180927	325.95	8	203521	XL	Black	-

Example - Adding a field with a sliding window

Load script

Create a new tab in the data load editor, and then load the following data as an inline load. Create the table below in Qlik Sense to see the results.

Transactions:
Load
*,
Window(Avg(transaction_amount),customer_id, 'ASC', -1, 1, 0, 1) as AvgCustTransaction;

Load * Inline [
transaction_id, transaction_date, transaction_amount, transaction_quantity, customer_id, size, color_code
3750, 20180830, 23.56, 2, 2038593, L, Red
3751, 20180907, 556.31, 6, 203521, M, Orange
3752, 20180916, 5.75, 1, 5646471, S, Blue
3753, 20180922, 125.00, 7, 3036491, L, Black
3754, 20180922, 484.21, 13, 049681, XS, Red
3756, 20180922, 59.18, 2, 2038593, M, Blue
3757, 20180923, 177.42, 21, 203521, XL, Black
3758, 20180924, 153.42, 14, 2038593, L, Red
3759, 20180925, 7.42, 5, 203521, M, Orange
3760, 20180925, 80.12, 18, 5646471, M, Blue
3761, 20180926, 3.42, 7, 3036491, XS, Black
3763, 20180926, 63.55, 12, 049681, S, Red
3763, 20180927, 177.56, 10, 2038593, L, Blue
3764, 20180927, 325.95, 8, 203521, XL, Black
];

Results

Results for adding ad field containing an aggregation filtered for specific values
transaction_id	transaction_date	transaction_amount	transaction_quantity	customer_id	size	color_code	AvgCustTransaction
3750	20180830	23.56	2	2038593	L	Red	41.37
3751	20180907	556.31	6	203521	M	Orange	366.865
3752	20180916	5.75	1	5646471	S	Blue	42.935
3753	20180922	125.00	7	3036491	L	Black	64.21
3754	20180922	484.21	13	049681	XS	Red	273.88
3756	20180922	59.18	2	2038593	M	Blue	106.3
3757	20180923	177.42	21	203521	XL	Black	92.42
3758	20180924	153.42	14	2038593	L	Red	165.49
3759	20180925	7.42	5	203521	M	Orange	166.685
3760	20180925	80.12	18	5646471	M	Blue	80.12
3761	20180926	3.42	7	3036491	XS	Black	3.42
3763	20180926	63.55	12	049681	S	Red	177.56
3763	20180927	177.56	10	2038593	L	Blue	63.55
3764	20180927	325.95	8	203521	XL	Black	325.95

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!

Leave your feedback here