How do you calculate percentage rankings in Alteryx?

[dvedwards01]

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. 

[Jim7]

Hi, you could accomplish this pretty easily by using a summarize tool to calculate the total Count (column B). You would then append that result to your original data set and use a formula tool to calculate the customized percentage rank for each country.

[danrh]

I'm not familiar with PERCENTRANK, but after playing with it for a couple of minutes, does the attached do more or less what you're looking for?

[MarqueeCrew]

@dvedwards01,

I may be over-engineering this, but I've got a percentile macro that will allow you to get the result that you're after.  I divide the result by 100 and apply the logic for {5,4,3,2,Check} as you have it in Excel.

Cheers,

Mark

[dvedwards01]

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

[JuneBug]

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!

[Linas]

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