Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
claude_jakob
New Contributor III

How to dynamically sum over periods

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

 

Labels (3)
1 Solution

Accepted Solutions

Re: How to dynamically sum over periods

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

image.png

View solution in original post

1 Reply

Re: How to dynamically sum over periods

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

image.png

View solution in original post