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