How to Calculate the Moving Sum

Goal

It is necessary to calculate a three year running total for every customer and each order date.

For example, a site that places an order on `12/5/2015 `wants to calculate total bookings from their site during the `12/5/2012-12/5/2015` period. If they place another order on `1/15/2016`, they need a sum of their total bookings during the `1/15/2013-1/15/2016` period. And so forth.

The sample data set is attached as SourceData.csv.

Learn

Calculate the moving sum with the function GROUPACCUMULATE.

Import the sample data set via an Import Job and add the data to a workbook, duplicate the source sheet and call it `MovingSum`.

On the sheet `MovingSum` apply the following formulas:

• On column `Customer`
`GROUPBY(#SourceData!Customer)`
• On column `Amount`
`GROUPACCUMULATE(#SourceData!Order_Date;INT(REPLACEALL(#SourceData!Amount;'(\\\$)';'')))`

This will deliver the expected result, which was downloaded and attached here as ResultSet.csv for quick reference.

Since the used function is a SortedGroupSeriesFunctionType, it will be necessary to reference the data from the source sheet. Therefore, the formula to convert a currency string into an integer is nested in the grouping function.

Attachements