All hail the Multi-Row Formula!! Alteryx Designer

We often come across cases where we are required to perform calculations between different rows of either the same column or different columns to derive insights. Examples include calculating the percentage difference or any other complex conditions. Excel offers an unparalleled experience in performing such calculations owing to its flexible nature.

However, many times, we notice excel hanging when we try to perform such operations on huge datasets, resulting in delay of our deliverable. It is one of the reasons, corporations across the world invest in alternative ETL tools to perform such complex operations to save both time and automate a process.

We will talk about how to leverage this functionality of performing row level interactions in Alteryx. Alteryx is a self-service ETL analytics tool with an intuitive user interface which can be used to Extract, Transform and Load Data.

There is an extremely useful tool called “Formula” in Alteryx which can perform complex operations like conversions, spatial, numerical, text operations, conditional functions etc. However, the formula tool only works on a column basis. It interacts with your data on a column basis, where in it fills or populates our rows in a column based on our input. It does not make rows inside a column interact with each other. So, what is the solution? Enter the Multi-Row Formula.

The Multi-Row Formula is available under the “Preparation” set. It allows us to utilize row data as part of the formula creation. It lets us decide how two or more rows must interact based on a condition. The Multi-Row Formula tool can only update one field per tool instance. If you would like to update multiple fields, a Multi-Row Formula tool must be added to your workflow for each field to be updated.

Let us go though an example by comparing the same scenario in both Excel and Alteryx.

You have found out that a particular material is being procured from the same vendor, in the same location and plant at different prices, and you are now tasked with finding out the percentage difference in price to see what the incurred revenue leakage is.

I can create a new column in F by the name Percentage Difference, and can enter the formula
((E3 — E2)/E3)*100 ( (Upper Value — Lower Value)/(Upper Value)*100) to calculate my percentage difference and copy paste the same formula for all the cells.

However, you will notice that the values populated in F3, F5 do not make any business sense. Ideally, from a business perspective F3 should not be calculated as the Material, Vendor, Location and Plant combination is different for row 3 and row 4. These are two different combinations and cannot be used together to calculate a percentage difference. This problem can be addressed with an if condition. Using an if condition, we can instruct excel to perform this operation if and only if the Material, Vendor, Location and Plant column’s values are same for the two rows that are being compared or that are being used to make the calculation.

Now, you can notice that the percentage difference is only calculated at instances where our condition is satisfied.

Let us take a look at how the same can be performed in Alteryx.

The configuration of the Multi-Row formula can be found below.

Similar to the formula tool, the Multi-Row formula tool offers you the feasibility of either updating an existing column or creating a new column altogether where in we can decide the Name and the datatype of the column.

Let us inspect each functionality of the tool. The variables tell us about the row levels. i.e., whether it is an active row, the preceding row, or the succeeding row. The configuration is as below.

Row — 1: The row before the active row.

Row + 0: This is the Active Row. The active row is where the result goes into.

Row + 1: The row after the active row.

As you can notice, when we select a variable, we can see all the columns that are mapped to that variable out of which we can make our selections. The condition or the formula is entered in the Expression tool. If we want to compare this to excel, [Row + 1: Price] is E5 and [Price] is E4. Once the condition is entered, the Multi-Row formula will populate these values to the entire column similar to the copy paste functionality we have done for excel.

The Num Rows field can be used to set the row variables that display as in the expression editor. When set to 1, these variables display Row + 1 and Row — 1. When set to 2, we can see Row + 1, Row — 1, Row + 2, Row — 2, Row 0. To put it in simple terms, if you want to use n rows for your calculations, you populate the value n in Num Rows.

You might be wondering, what about our condition we have put in excel, where we wanted to calculate the percentage difference if and only if my Material, Vendor, Location and Plant are same for the two rows that are being used. Well, Alteryx has got you covered there. The Multirow formula has a GroupBy option which provides the exact same functionality. When you select the columns in the Groupby field, Alteryx will perform your condition entered in the Expression Window only if the combination of the column selected in the GroupBy field match between the rows.