Last week, we looked at how to rank in Alteryx using the Record ID and Join tools. This week, I'll be demonstrating how to rank using the Multi-Row Formula tool. I'll be using the dataset from Alteryx's Weekly Challenge #9 to showcase the tool.
First, a little about the Multi-Row Formula tool
The Multi-Row Formula tool, found under the 'Preparation' tool palette, allows you to refer back to other rows of data in your formula. This is useful when you need a formula that will iterate through your data based on row-level parameters.
The Multi-Row Formula configuration window can be broken down into three sections.
-
Section A: User can choose to modify an existing column or create a new one. In addition, the user can specify the number of rows to refer to in the formula. For example, if each row of a dataset contained monthly grades of students and you wanted to calculate a 6-month rolling average for grades, you'd want to specify the number of rows as 6. Finally, you can specify how you want to display values for rows that don't exist.
-
Section B: Here, the user can view the variables at their disposal and browse functions. You can also access saved expressions from this panel.
-
Section C: This is where you'll write the formula, or expression, that'll iterate through your data.
Solution
- Just like our previous solution using Record ID, we need to sort our data in descending order by count using the Sort tool.
2. Second, we want to use the Multi-Row Formula Tool to create a new 'rank' column using an iterative expression that references the previous row in the data.
I'm creating a new column called 'Rank.' In this particular example, we also want the 'Values for Rows that don't Exist' to be '0 or empty.' Any other option will result in null values. Finally, we only need to reference one above or below our reference row.
Let's break down the expression. Keep in mind that we ranking in 'rank dense' fashion.
-
If [count] != [Row-1:count] : here, we are seeing testing if the count of the current row of data is not equal to the count of the previous row of data.
-
THEN [Row-1:Rank] + 1 : if the above statement is true, then the rank becomes the row number of the record plus 1.
-
ELSE [ROW-1:Rank] ENDIF : if the above statement is false, then the rank becomes the row number of the previous record.
Alternatively, we could written the expression in the following way:
IF [count] = [Row-1:count] THEN [Row-1:Rank] ELSE [Row-1:Rank] + 1 ENDIF