Our company has sales people in the field that might have sales in various Regions. I want to assign the Region based on Region that has the greatest number of sales for that particular salesperson. For example, in the table below, I would want all of Salesperson A's sales assigned to the South Region, even though he has some sales in other Regions since he had the greatest number of sales in the South Region: SalespersonRegionSales AmountSalesperson ASouth1,000Salesperson BSouth50Salesperson AWest12Salesperson ACentral55Salesperson AEast25Salesperson BCentral52 What kind of workflow could I use to come up with this? Also, in the event of a tie between 2 Regions, I have a table that has a Rank on it that would then determine which Region would be used. I would also need this to be part of the workflow. I am still relatively new to Alteryx and would appreciate any help given. Thanks all! Region Rank.xlsx

Assign Region based on values in another field

Our company has sales people in the field that might have sales in various Regions. I want to assign the Region based on Region that has the greatest number of sales for that particular salesperson.

For example, in the table below, I would want all of Salesperson A's sales assigned to the South Region, even though he has some sales in other Regions since he had the greatest number of sales in the South Region:

Salesperson	Region	Sales Amount
Salesperson A	South	1,000
Salesperson B	South	50
Salesperson A	West	12
Salesperson A	Central	55
Salesperson A	East	25
Salesperson B	Central	52

What kind of workflow could I use to come up with this?

Also, in the event of a tie between 2 Regions, I have a table that has a Rank on it that would then determine which Region would be used. I would also need this to be part of the workflow.

I am still relatively new to Alteryx and would appreciate any help given.

Thanks all!

Region Rank.xlsx

Workflow

Preparation

Data Investigation

Accepted answers

DataNath

@arnettmh just add another join like so If you don't want the 'Sales Total' field in the output then simply untick it in the Join tool configuration:

Assign salesperson to region - original and max sales.yxmd

All comments

oly

Hi @arnettmh,

Check attached workflow. Not sure if you wanted maximum or minimum ranking value as priority, I used the minimum. Hope the logic is very straighforward, just combination of joins and summarize tools.

Best,

Ooy

Region Rank Sales.yxzp

arnettmh

Thanks for the really quick response. Unfortunately, I am unable to open the file as I am on an older version of Alteryx (2021.1). Are you able to screenshot the workflow?

arnettmh

I was able to open the file now. I feel like this "almost" gives me the solution I need and I really appreciate your help. However, in the final output, I would need the Region with the most sales applied to that salesperson and their total sales summed up for that Region.

For example, using the sample numbers above, the final output would have Salesperson A assigned to the South region since that is the Region that had the largest sales amount and the total sales amount for Salesperson A would be 1,092. For Salesperson B, they would be assigned to the Central Region since they had the most sales in that Region (52).

So, the final output would look like the following:

Salesperson	Region	Sales Total
Salesperson A	South	1,092
Salesperson B	Central	102

DataNath

How does this look @arnettmh? Have increased one of the sales values (Salesperson A - West) to match another (South) to show it working with the rank table as well:

Assign salesperson to region.yxmd

arnettmh

This is awesome! I like the approach. What if I wanted to keep the original Region data (field named Region would become "Original Region") and add another column called (Max Amount Region), how would I do that? Output would look like the below:

Salesperson	Original Region	Max Amount Region	Sales Amount
Salesperson A	South	South	1,000
Salesperson B	South	Central	50
Salesperson A	West	South	12
Salesperson A	Central	South	55
Salesperson A	East	South	25
Salesperson B	Central	Central	52

DataNath

@arnettmh just add another join like so If you don't want the 'Sales Total' field in the output then simply untick it in the Join tool configuration:

Assign salesperson to region - original and max sales.yxmd

arnettmh

Thanks for all the assistance!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703