Assign Rank till a certain total

Good morning, I have a dataset where the field 3 (percent) is calculated based on field 2 (AMOUNT). Now I want to assign a (NEW) rank field, in ascending order STARTING FROM 1, but it has to be done only until the sum of percentage is 80. After that, everything else should be 0 in the new field (rank).

Attaching a test file. Please let me understand how to solve this. Thanks.

ANUDEEP

Alteryx Rank Test.xlsx

Regex

Tips and Tricks

Transformation

Preparation

Accepted answers

jdminton

Based on your description, my first thought was iterative macro, but with your data, you can avoid that. I used a running total and formula tool to rank the first up to 80%

Rank.yxmd

Hsandness

@Anudeep_Yalamuru ,

If you want the A and B to have their own rank then you could add a Multi-Row Formula tool to give each ID it's own Record ID column beginning with 1. The B data in the workflow is just the A data copied.

Hope this helps.

Rank.yxmd

All comments

jdminton

Based on your description, my first thought was iterative macro, but with your data, you can avoid that. I used a running total and formula tool to rank the first up to 80%

Rank.yxmd

Anudeep_Yalamuru

@jdminton if we are doing this for multiple items, is iterative macro only solution? for example, consider the above example as identity A and if same has to be done for B, C, etc.? How do I modify this ?

Thanks,

Anudeep

jdminton

No iterative macro should be necessary. For the running total, you would select to group by the field for the identifier is all.

Anudeep_Yalamuru

Yes, I did that. Only issue is I'm not getting the rank starting from 1 for the following ones. Lets say we have A, B, C in the above scenario in one file one after other. I figured out first 10 in A constituting 80%, so their Rank is 1-10. Now I want the same for B and C as well with Rank starting from 1.

Thanks,

Anudeep

Hsandness

@Anudeep_Yalamuru ,

Hope this helps.

Rank.yxmd

Anudeep_Yalamuru

Awesome, Thanks @Hsandness and @jdminton

jdminton

I see what you're saying. Yes, you will want a multi-row formula if you are looking at the data this way.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703