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:
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.
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:
Finally, add a Hide Columns step to hide the Moving average column, and choose a Line chart for the chart type.