Skip to main content
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.
idamountnotereference
0300active_row_minus_two [-2]
1100previous_row [-1]
2200active_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:
idamountnewAmountexplanation 
11000previous row is not available so defaults to the value in Step 3 below
2200200active_row_minus_two is not available so defaults to the value in Step 3 below
3300300200 + 100 - 300 = 0
4400400200 + 300 - 400 = 100
55000300 + 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:
monthamountchurn*monthlyexplanation 
11000.0083_previous row is not available so defaults to the value in Step 3 below*
299.160.0083100*0.0083
398.340.008399.16*0.0083
497.520.008398.34*0.0083
596.700.008397.52*0.0083

​
Configuration

The Multi Row tool is comprised of three required input.
1

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)
2

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 (*).
3

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)

​
Example

I would like to forecast revenue 11 months into the future based on a churn rate and growth rate
I want to calculate a monthly difference in percentage
⌘I