Multi Row
Create advanced loop-based calculations
The Multi Row tool is slightly complex to understand but very powerful. With the Multi Row tool you can use row data as part of your calculation while looping through your dataset. The calculations can also be self-referencing meaning you use the previous results inside the same column calculation the next calculations.
To illustrate, visualise your dataset vertically. The italic columns are not actually present in your dataset but only shown here to make the tool easier to understand.
| id | amount | note | reference |
|---|---|---|---|
| 0 | 300 | active_row_minus_two | [-2] |
| 1 | 100 | previous_row | [-1] |
| 2 | 200 | active_row | [0] |
Let’s say you wanted to create a calculation where you added the next and previous row while deducting the active row. In Step 2 (see Configuration below), you would input [amount][-1]+[amount][-2]-[amount][0]. That would give you the following results:
| id | amount | newAmount | explanation |
|---|---|---|---|
| 1 | 100 | 0 | previous row is not available so defaults to the value in Step 3 below |
| 2 | 200 | 200 | active_row_minus_two is not available so defaults to the value in Step 3 below |
| 3 | 300 | 300 | 200 + 100 - 300 = 0 |
| 4 | 400 | 400 | 200 + 300 - 400 = 100 |
| 5 | 500 | 0 | 300 + 400 - 500 = 200 |
You can also reference the column that you are working with to create self-referencing calculation. In the example below, we would like to forecast revenue churn into the future. We assume a 0.83% churn rate per month and start with a revenue of 100. We input [revenue][-1]*(1-[churn-monthly]). That would yield the following results:
| month | amount | churn*monthly | explanation |
|---|---|---|---|
| 1 | 100 | 0.0083 | _previous row is not available so defaults to the value in Step 3 below* |
| 2 | 99.16 | 0.0083 | 100*0.0083 |
| 3 | 98.34 | 0.0083 | 99.16*0.0083 |
| 4 | 97.52 | 0.0083 | 98.34*0.0083 |
| 5 | 96.70 | 0.0083 | 97.52*0.0083 |
Configuration
The Multi Row tool is comprised of three required input.
Name New Column
Input the name of the new column that will be created by the Multi Row tool.
If you want to use the self-referencing method, you should input the name of your existing column (case sensitive - see Forecast Revenue example below)
Input Formula
Input your calculation formula using [-rowNumber] to access previous rows. You can reference existing columns in your dataset by starting to type the column name. Alternative you can write it out with hard brackets ([]) around the column name. In other words, to access the row value ten rows before (up in your dataset) of a revenue column you should input [revenue][-10]. You can also use the most basic mathmetical operations such as addition (+), subtraction (-), division (/), multiplication (*).
Input Fallback Value/Calculation
You should input a backup value or calculation in case the previous rows can’t be accessed because they don’t exist in your dataset. For instance, with the [revenue][-10] formula above, the first 10 rows of your dataset can’t access the active row minus 10 rows because it simply doesn’t exist. Input the fallback formula using the same syntax as in Step 2 - only without the row navigation ([-rowNumber]).
When To Use
Generally speaking, the Multi Row tool is built for primarily self-referencing calculations where you need to use the results that the calculation create while it calculates.
- Forecasting revenue or churn where you use the previous results as you calculate future results (i.e. self-referencing) - the same methodology works for all forecasting, not just revenue and churn
- Creating advanced row-based calculations where you use previous and current row values to generate results (you can also use the Compare tool for that but it requires more tools)