Assign rank based on value

Hello all,

I'm trying to achieve something as illustrated in the attachment, which is to assign a rank based on a hypothetical value to be looked up in another table.

I found a past discussion that almost does what I need it to do [link], but not for the format in which I have my data and I haven't been able to figure out a solution.

I would appreciate any help, thank you!

Assigning rank.xlsx

Datasets

Help

Accepted answers

Luke_C

Hi @golfromeo

I prepared the attached workflow, from what I can gather we're looking for the record that is closest to the hypothetical sales without going over.

I join the two data sets based on segement
Then I filter out records where sales exceed the hypothetical sales
Then I sort by segement/sales
Then sample the first record for each sale (the highest sales without going over)

New Workflow1.yxmd

All comments

mceleavey

Hi @golfromeo ,

those number are not related. Can you explain why the combination of the two inputs would give the output required?

Why is Segment 2 Ranked 5? Why is Segment 1 Ranked 2 etc.

We will need to know the logic. Is it the hypothetical sales determine the rank by looking up the closest value in the corresponding segment in Input 1?

Thanks,

Luke_C

Hi @golfromeo

I prepared the attached workflow, from what I can gather we're looking for the record that is closest to the hypothetical sales without going over.

I join the two data sets based on segement
Then I filter out records where sales exceed the hypothetical sales
Then I sort by segement/sales
Then sample the first record for each sale (the highest sales without going over)

New Workflow1.yxmd

golfromeo

Thanks for your reply and sorry if I didn't explain it clearly enough at first. The idea is to know what rank would this company have if they achieved sales of a certain amount. For example, in Segment 1, if they achieved sales of 8,500, they would be positioned 2nd replacing Carrier B. In Segment 2, by achieving 7,500, they would be 5th replacing Carrier E. Thanks again.

golfromeo

This works, thanks for your help!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280