A question that gets asked regularly is how to calculate a rolling measure over a period of N-months (or weeks or days). For example a 12-month rolling total or a 4-week rolling average. There are several ways to do this. The easiest way of doing this is to use the accumulate setting under the expressions tab of chart property.
With this setting, the values of the selected metric will be accumulated. But sometimes, the requirement is to have the first data point accumulated as well. For example, to show the accumulated sales for 12 months where the first month displayed should be the sum of the previous 12 months and the following months should accumulate on top of this value by adding one month at a time. One way of achieving this is to use the rangesum() and the aggr() functions together.
Let us first look at Rangesum(). It is a range function that returns the sum of a range of 1 to N arguments Together with the above() function, you can calculate the sum of the last 12 months;
rangesum( above( sum(Sales),0,12) )
This expression will return the sum of sales evaluated for each month as they appear on the 12 rows above the current row.
The next step is to combine RangeSum() with the aggr() function to aggregate the sum of sales for the 12 months at the month level.
sum( aggr( rangesum( above( sum(Sales),0,12) ),Month))
Using the Aggr() function, it is possible to calculate the accumulated sum for any other dimensions in addition to the time dimension. For example, the 12 months accumulated sum of sales by store can be calculated as;
sum( aggr( rangesum( above( sum(Sales),0,12) ),Month, Store))
Optionally you could also clear any selections on the month field as the calculation should reflect the accumulated sales for the previous 12 months, regardless of selections. This is achieved by using the set analysis “{$<Month>}”.
sum( aggr( rangesum( above( sum( {$<Month>} Sales),0,12) ),Month))
One important thing to notice with this solution is the sort order inside the aggr() function. If the sort order is not correct, the aggregated sum of the 12 months would not be correct. QlikView always sorts the groupings (in this case the groups defined by the second parameter of the aggr() function) according to the load order. If the data is sorted by date during the load, QlikView will use this order and the aggr() function will show the correct totals for the desired time frame.
In summary, the accumulation option on the expression tab is the simplest way of achieving accumulations, but the use of rangesum() and aggr() functions are alternative ways of accumulating the data in more complex situations.
