How to dynamically sum over periods

[claude_jakob]

Hi

for an analysis I need to be able sum up sales of a number of periods in a rolling manner. As example to create the value with 12 Months rolling for October 2018 I need to sum-up sales Oct 2018 to Nov 2017, for September 2018 value the sum of Sep 2018 to Oct 2018 is required

I have sales data by month available. In addition I have a Month_ID (running number) available well. How to I define the expression in Qlikview to achieve the correct aggregated sales. In excel I would use the formula =sum(offset()).

I have attached an excel file with the case. 2 Tabs ('Calendar ' with Month_ID and YR_MTH values, 'Data' with the sales data by month). The data tab also contains the expected values I need to see in the table.

The rolling aggregation period should be dynamic hence I have also included a variable in Excel to simulate this.

Thanks for help

Claude

[sunny_talwar · Accepted solution]

Try like this (if you have QV12 or above)

=Aggr(RangeSum(Above(Sum({<YR_MTH>}Sales), 0, vVar)), (YR_MTH, (NUMERIC)))

Where vVar is set using an input box