Alternative to the Excel "rank" function

hi Alteryx community,

we're (our department) converting analytics trainings for non data savvy / technical people from Excel to Alteryx and came across a challenge - Alteryx does not have a "rank" function, which is needed for one of the exercises. Since we market Alteryx as "better and simpler than Excel" I am looking for the simplest solution (not a macro) for non technical people.

Use case – we have 15 branches and 3 indicators per branch. We want to select 5 most risky branches (based on the indicators).

Solution in Excel
- Use the "rank" function for all 3 indicators, sum the rankings and rank the "sum of ranks" --> select top 5

Solution in Alteryx (my current idea):
- Sort data by 1st indicator & add "record ID", sort by 2nd & add "record ID", sort by 3rd indicator & add "record ID"
- Sum the indicator ranks (all three record ID columns) and sort the "sum of ranks"

This seems unnecessarily complex. Any other ideas (even if totally out of the box)?

Thank you.

Preparation

Accepted answers

danilang

Hi @janez_007

Here's another option

Transpose the data using branch as the key. Sum the indicators and join back to your main data on Branch. Sort on TotalRisk descending giving you

Dan

Rank.yxmd

All comments

grossal

Hi @janez_007,

Not sure if I understand it correctly but here is what I would do:

- Sort by all criteria at once

- Use 3x Multi-Row Formulas to create the Ranks (with different group by option each time)

- Build sum

- Sort

It doesn't really save much, but at least a bit.

Best

Alex

RolandSchubert

Hi @janez_007 ,

in general, Alteryx is better and simpler than Excel, even though there are some functions that look easier with Excel . I have a different approach, not sure if this is really "easier", but maybe an option.