How to do a rolling sum in the Data Pipeline

Data Pipeline

We’re creating a chart that gets the count of users per day, and we want to include a column that calculates the rolling sum of the count of users in the last seven days. This differs from a standard running total, because it only includes the sum of the previous seven rows of data.

Start by building a chart for the count of users per day. So far, our chart looks like this:

Sample chart to start

Add a new column in the Pipeline to calculate the moving average. Select +Add Step, choose Add Column, and select Moving average in the formula dropdown. Then, select the Count of User Id column, and enter “7” in the Trailing Rows input.

Add Column step in the Pipeline and make the formula Moving average

The moving average is simply the sum of the last seven values divided by seven. Therefore, we can get our rolling sum by multiplying the moving average by seven. Add a new column to do that:

Add another column in the Pipeline; formula type is Custom

Finally, add a Hide Columns step to hide the Moving average column, and choose a Line chart for the chart type.

Rolling sum final chart result

Related Data Pipeline Help Articles

See more