Calculate 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 is different from a standard running total, because it only includes the sum of the previous seven rows of data.

  1. Start by building a chart for the count of users per day.

    Create chart

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

    Add Column in Pipeline to define Moving average

  3. 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 exactly that:

    Add another Column to define Rolling sum

  4. Add a Hide Column step in the Pipeline to hide our Moving average column. Click Apply and Close.

    Add Hide Columns step in Pipeline to hide the Moving average column

  5. Choose a Line chart from the chart types and click Save Chart above the chart preview to save your chart to your dashboard.

    Final result, line chart

Related Data Pipeline Help Articles

See more