Finding the rolling sum of a variable can allow you to:
- Calculate the headcount by getting the rolling sum of new hires
- Track the user count of an application by using the rolling sum of the new users etc.
The sum function, span time modifiers and the timeStep helper variable are the key tools that should help us accomplish this.
As the name suggests, the sum function returns the sum of the parameters passed.
The timeStep helper, also known as t or date returns the column number of a given cell.
If we write something like sum(Variable[0:t]) we should get the rolling sum of the variable.
We may also find the need to find periodic rolling sum. A use case would be to find the YTD Revenue. To implement this, we can get creative with the helper variables we and plug an appropriate expression in the time modifier. A formula similar to sum(Revenue[ t-month+1 : t] ) should do the trick. The month helper returns the month for the current column (i.e. 1 for January, 2 for February…) which is why writing t-month+1 : t in the time modifier should give us the span of the revenue from the last January to the current month.