If you have used Tableau, you will have noticed that there are four different types of rankings you can assign to a table. Here I will explain one way of achieving each of these in Alteryx. We will use one simple sales data set throughout, shown below.
Rank Unique (1, 2, 3, 4)
Rank Unique should give each row a unique number, even if they are tied (like North and East are in our input data).
First we sort the data based on sales descending. Then, we add a record ID, which is unique by default - and we're done.
Here's the workflow:
Rank Dense (1, 2, 2, 3)
Rank Dense should give tied rows the same number rank, and the next record that isn't tied the next number up.
We again start by sorting sales descending. Then, we use the Multi-Row Formula tool to compute the rank. The formula we use is:
IF [Row-1:Sales] = [Sales] THEN [Row-1:Rank (Dense)]
ELSE [Row-1:Rank (Dense)] + 1
ENDIF
Lets break this formula down. When calculating the multi-row formula output, Alteryx runs through the data row by row, in order - this is important in understanding the calculation. Imagine starting with the first row. It first checks if the sales amount equals the sales amount of the row above. However, since it is the first row, there is no row above it - which can be quite confusing. The Multi-Field formula tool has a setting that allows you to set the values for rows that don't exist.
Here we use the "0 or Empty" option. This means the value of [Row-1:Sales] will be set to 0 (zero). Now we can see that the first row doesn't pass the condition of the IF part, so it defers to the ELSE part. The formula says to set the Rank (Dense) value of the first row to [Row-1:Rank (Dense)] + 1. This can be quite confusing too, since we are referencing the field we are currently creating with the calculation. But remember, the multi-row works row by row - so the value for a previous row can be referenced. Again, however, the value doesn't exist for the first row, since there is no row above it. So it is set to 0 (zero). Then we add 1 to it to get the value for the first row: 1.
For all the following rows, the calculation should be more clear. If the sales amount of the row equals that of the row above, we give it the same rank as the one above. Otherwise, we add 1 to it. Since we have sorted the data by sales descending, in this way we get the dense (1, 2, 2, 3) ranking. Here's the workflow and output data:
Rank Competition (1, 2, 2, 4)
Rank Competition is the same as Rank Dense, except the next row that isn't tied should be given it's rank unique value.
To do this we follow the same steps as we did for the rank unique workflow: sort on sales descending, then add a record ID. Next we add a multi-row formula:
IF [Row-1:Sales] = [Sales] THEN [Row-1:Rank (Competition)]
ELSE [Rank (Unique)]
ENDIF
This IF part of the statement stays the same as the rank dense formula. This is because we still want rows that are tied to get the same ranking. However, for the next row that isn't tied (the ELSE part of the statement), we set the value equal to the row's Rank Dense value. In this way, we get the (1, 2, 2, 4) ranking. Here's the workflow and output data.
Rank Modified Competition (1, 3, 3, 4)
Rank Modified Competition is the same as Rank Competition, except that tied rows should be given the Rank Unique value of the last tied row.
This is the trickiest rank type. To do this one we will need Rank Competition. So:
Follow the same steps as we did for Rank Competition: sort on sales descending, add a record ID and the multi-row formula we used for Rank Competition. Now, for tied rows, we need to know the Rank Unique of the last placed tied row. To do this, we sample the data, configuring for the last 1 row, grouping by Rank Competition.
Finally, we need to join this data back to our Rank Competition output, joining on the Rank (Competition) field. For tied rows, this will bring on the Rank Unique value of the last placed row; for other rows it does nothing. Within the join, we can rename the Rank (Unique) field that came from the sample output to Rank (Modified Competition).
Here's the workflow and output data:
