Assign Rank till a certain total

[Anudeep_Yalamuru]

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

[jdminton · Accepted solution]

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%

[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 · Accepted solution]

@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.

[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.