Alternative to the Excel "rank" function
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Hope this is helpful.
Best,
Roland
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thanks, this looks awesome!
