How do you calculate percentage rankings in Alteryx?

Question

I'm currently working on a workflow, and have been unable to find any posts that deal specifically with ranking results based on a manual percentage rank that I would assign (not quartiles). In Excel, you can use the function "PERCENTRANK" to determine what percentile your results fall into, and even assign ratings based on the percentile. As an example, if I wanted to see what countries were in the top 89% of all countries in the list, I could assign a field a rating of a "3" to say that it was in a higher tier of results than a country with a count that was at 66%. I'm trying to avoid using quartiles, as I need to manually segment percentage ranks of countries (not evenly divide them into quartiles). I've attached the Excel file with counts for review/reference. Any advice on a direction forward would be greatly appreciated.

Percentage Rank By Country.xlsx

Linas · Answer

Thanks @danrh for the solution!

@JuneBug you can transpose your data so that all values and field names are in two columns: Name and Value. Then, when you calculate percent rank, group by the Name field (the summarize and multi-row formula tools need to be adjusted). No need for a batch or iterative macro!

Cheers

JuneBug · Answer

Thanks danrh for this solution! I followed it and worked perfectly. Thank you!

I have a follow-up question. I need to repeat this process about 40 times (e.g. rank 40 other variables). How can I automate this entire workflow? I see some posts about macros and iterative macros, but I fear those may not apply to this scenario because part of this workflow involves defining new variable names. I am happy to formulate a pattern to the new variables' names (e.g. rank_field1, rank_field2, rank_field3, etc.). What would you recommend?

Thank you!

dvedwards01 · Answer

Hi Mark,

Thanks for the reply. I tried running the macro, but received the following error:

---------------------------
Missing Macros
---------------------------
This workflow references macros (.\Percentile_Batch.yxmc) that are missing or inaccessible. Saving this workflow will permanently disable these tools.
-----------------------

So it looks like I need the "Percentile_Batch" macro. I tried looking this up in your crew macros, but couldn't find it. Could you send that along so I could check it out and see if this solves the issue?

Thanks, David