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.
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.
timeStep helper, also known as
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.