topic Re: How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function in App Development

How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function

guilherme-matte — Wed, 13 Aug 2025 10:30:36 GMT

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.

Cheers!

Re: How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function

marcus_sommer — Wed, 13 Aug 2025 12:39:20 GMT

The help states them clearly as expression which means they are in general dynamically. In your case you may try to apply something like this:

window(..., addmonths(myDate, -3), myDate) as ...

Re: How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function

guilherme-matte — Wed, 13 Aug 2025 13:15:33 GMT

Hey Marcus!

Thank you for your input, really appreciate it.

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.

Cheers!

Re: How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function

marcus_sommer — Wed, 13 Aug 2025 14:02:00 GMT

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.

Re: How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function

howdash — Thu, 14 Aug 2025 01:55:55 GMT

@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;

Re: How to Create a Dynamic Sliding Window in Qlik Script Using Window() Function

marcus_sommer — Wed, 13 Aug 2025 14:40:07 GMT

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.