These are two essential tools for any Alteryx user. The 'Multi-Field Formula' is used when applying calculations across different fields (or columns) and the 'Multi-Row Formula' for rows.
1) Multi-Field Formula
In this example we want to calculate the total sales & cost of our given products as shown.
To do this we're going to multiply both the 'Sale(s)' field and 'Cost' field by the 'Quantity' field.
First we select our desired current fields as 'Sale(s)' and 'Cost' so we can use them in our Multi-Field Formula. Next we have select 'Copy Output Fields and Add' to create a new set of fields with the prefix 'Total_'.
Next we create our expression [_CurrentField_]*[Quantity] which can be done by clicking on the variables above. This will create a new column for each current field we have selected (2) with it's values being the result of our equation:
2) Multi-Row Formula
The Multi-Row Formula instead works between rows and although limited to an individual column, we have more flexibility in the expressions we can create. In this example we want to create a 'Region ID' for each 'Year' & 'Quarter'
To do this we need to use the Multi-Row formula tool to call upon previous rows, yet remain restricted to each region as shown below:
Again, we will be creating a new field with this calculation and so the 'Create New Field' is selected. Group by 'Region' is selected to apply our following expression to each set of Regions independently. We can create our expression [Row-1:Region ID] + 1, again this can be added by using the variables tab. This equation calls upon the row before in our new column (Row-1) and adds 1 to create the next number in our 'Region ID'.
(Due to our selection of the 'Values for Rows that don't exist' we will receive 1 for our first row in the calculation)
