How to Rank by Group in Alteryx - Part 2 - Modified Competition Ranking

by Liu Zhang
pedestal, ranking, podium, winner, dark, 3d, top-light, bw, black and white
Photo by Joshua Golde / Unsplash

Compared to standard competition ranking show in part 1, modified ranking does the opposite treatment for equally ranked record. Given we need to know how many repeats there are, we cannot use Multi-row Formula directly as before, as it computes in a top down order.

Instead, we will need to reverse the sorting order of the data, where the last term appear first

Sort Row ID descending

We know the last person should be ranked last, we can work backwards in that case.

If the current row is not equal to previous row (i.e. lower ranked measure), then there is no continued repeating ranking, we have reach the floor value for the rank, hence we will use the current Row ID. (Equal Ranking Count is used as an indicator in the formula)

Output of the Multi-row Formula
Sort by Row ID ascending again to return original order

Note: There is no rank 1 in modified rank if there is a joint first, i.e. the measure is only ranked higher if it is greater than the rest.

Other than joint 1st, the other cases are as expected

Modified competition ranking is much less common than Standard competition ranking, nevertheless, it is an option us to use if needed.


Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.

Thu 22 Jul 2021

Fri 18 Jun 2021

Wed 14 Jul 2021