How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function
Hello Team,
I’m trying to create a sliding window calculation directly in the Data Load Editor using the Window() function.
My goal is to apply this sliding window on transactional data to calculate metrics (e.g., average unit price) for the last 3 months. The challenge is that the number of rows in my dataset varies by period and grouping, so the start and end range of the window needs to be determined dynamically rather than using a fixed offset tipicaly this would be the case of RANGE framing if I'm not mistaken.
Table_X:
NoConcatenate
LOAD
[Purchase #],
[Invoice Date],
[Month],
[Company],
[Client],
[Segment],
[Index],
[Constituents],
[Package],
[Description],
[Unit Price],
// Window Calculation
// Avg unit price across all transactions in the 3-month window
Window(
Avg([Unit Price]),
[Constituents], [Package], [Description],
'ASC', Month, RANGE_START, RANGE_END
) AS [Avg_3M]
RESIDENT SomeSourceTable;
What I need help with:
How can I make RANGE_START and RANGE_END dynamic so that they always capture the last 3 months based on the current row’s month in the transactional data?
Is there a way to pass a calculated start/end position to Window() inside the script rather than hardcoding it?
I’ve reviewed the Qlik documentation on Window(), but all the examples use fixed numeric offsets.
I've tried using expressions before, as well as your suggestion; however, I face the following issues whenever adding expressions to the range_start and range_end:
The following error occurred:
Window start expression is invalid
The error occurred here:
Tx_with_3m:
NoConcatenate
Load
[Purchase #],
[Invoice Date],
[Month],
[Company],
[Client],
[Segment],
[Index],
[Constituents],
[Package],
[Description],
[Unit Price],
Window( Avg([Unit Price]),
[Constituents], [Package],
'ASC', Month, AddMonths(MonthStart(Month), -3), AddMonths(MonthStart(Month), -1)
) AS [Avg_3M]
Resident tempMaster
The following error occurred:
Window start expression is invalid
Basicaly:
The following error occurred: Window start expression is invalid
This happens only when expressions are on those fields, if I change it for an offset like (-3, -1) it works. However, since it's transactional data, the offset depends on the number of records.
Does it occur immediately which means the syntax isn't accepted or during the execution which would mean that there is a not intercepted run-time error?
You may try to out-source this expressions by calculating the offset at first and applying the window within a following load-step - means executed in a preceding part or maybe in the load which created the source for the used resident-table.
@guilherme-matte it sounds like you're misinterpreting the purpose or RANGE_START and RANGE_END parameters of the Window function.
Purpose of Range Start and End Expressions
RANGE_START and RANGE_END are not meant to be the "searching" expressions. Meaning they're not created to find all records three months prior to the month on current line.
Instead, they're meant to be more of a "filter" expressions. RANGE_START and RANGE_END are meant to specify how many rows before and after the current row you would like the Window function to consider to calculate the average unit price.
Why You're Seeing an Error for Range Start
The reason you're getting an error for the Window start expression is because the AddMonth function returns a positive number - like 45778 for May 1st, 2025. But the RANGE_START expression is expecting a negative number - like how many of previous rows you want the function to consider.
Calculating Prior Three Months' Average
As for calculating average for the prior three months, you can do something like this:
Table_X:
LOAD *,
// add key that will be used to map prior 3 months' average unit prices
AutoNumber(
Constituents & '|' &
Package & '|' &
[Item Name] & '|' &
Num(Month)
) as avgKey
;
LOAD
[Purchase #],
[Invoice Date],
[Month],
[Company],
[Client],
[Segment],
[Index],
[Constituents],
[Package],
[Description],
[Unit Price]
RESIDENT SomeSourceTable;
// loop through each available month
For Each vMonth in FieldValueList('Month')
// identify month to start calculating averages from
Let vStartMonth = AddMonths(vMonth, -3);
// identify transactions of prior three months
transactions_prior_3_months:
Load Constituents,
Package,
Description,
[Unit Price],
Date('$(vMonth)') as Month
Resident Table_X
Where Month >= Date('$(vStartMonth)')
and Month < Date('$(vMonth)')
;
// calculate prior 3 months' average unit price
averages:
Load Constituents,
Package,
Description,
Avg([Unit Price]) as prior3MonthsAvgUnitPrice
Resident transactions_prior_3_months
Group By Constituents,
Package,
Description
;
// create mapping table containing keys to average prices
averagesMap:
Mapping
Load AutoNumber(
Constituents & '|' &
Package & '|' &
Description & '|' &
Num(Date('$(vMonth)'))
) as avgKey,
prior3MonthsAvgUnitPrice
Resident averages
;
Drop Tables transactions_prior_3_months,
averages
;
Next vMonth;
// map prior three months' average unit prices to each record (where prices are available)
// make average price null if average price in prior 3 months is not available
transactions:
Load *,
ApplyMap('averagesMap', avgKey, Null()) as prior3MonthsAvgUnitPrice
Resident Table_X
;
Drop Table Table_X;
Beside the current struggles with the window-feature be aware that there might be other ways to get the wanted information, for example with classical aggregation-loads with group by and/or using interrecord-functions like peek() and previous().
All methods will have their pros and cons and it will probably be depending on the overall requirements which one is the most suitable.
