Hi All, I've gotten some great help so far, so thank you for that! I think this time I have more of a formula question rather than what tool I'd use. I have a table of the following: Product CodeGenderCountryQuantity - PromisedSize CodeRatioQuantityProduct 1MaleCanada125S.337.5Product 1MaleCanada125M.562.5Product 1MaleCanada125L.225 I want the Quantities with decimals to be rounded, but I want it to total the Quantity - Promised quantity. So, rounding up would push the total quantity for Product 1 - Gender - Country - would exceed the 125 units "Quantity - Promised". There's a cumbersome process right now in Excel, but I was wondering if anyone had any guidance on how to approach this.

Rounding Values to Total

Hi All,

I've gotten some great help so far, so thank you for that!

I think this time I have more of a formula question rather than what tool I'd use.

I have a table of the following:

Product Code	Gender	Country	Quantity - Promised	Size Code	Ratio	Quantity
Product 1	Male	Canada	125	S	.3	37.5
Product 1	Male	Canada	125	M	.5	62.5
Product 1	Male	Canada	125	L	.2	25

I want the Quantities with decimals to be rounded, but I want it to total the Quantity - Promised quantity. So, rounding up would push the total quantity for Product 1 - Gender - Country - would exceed the 125 units "Quantity - Promised".

There's a cumbersome process right now in Excel, but I was wondering if anyone had any guidance on how to approach this.

Optimization

Accepted answers

jdunkerley79

I wasnt recomputing the Quantity at the start

I have added this

I have also added the other two picking methods (Largest Decimal Diff, Largest Percentage Diff)

New version attached

Allocate Three Methods.yxmd

All comments

JoshKushner

How are you getting fractional quantities? Could there be a fix earlier in your flow to give you whole quantities?

thelegazy

Hey Josh,

I'm using the following:

https://community.alteryx.com/t5/Data-Preparation-Blending/Multiplying-Values-by-ratio-from-different-Input/m-p/89534#M17296

jdunkerley79

I suggest the following:

- Allocate a 'base quantity' equal to the Floor of the Quantity

- All will now be within 1 of the target quantity

- Compute the residual

- Filter down to just those, not at the exact quantity

- Of those you 'pick' however many you need and add 1 to them

The picking is where it gets interesting. I have used 3 methods in the past:

- Random

- Largest Percentage difference

- Largest Decimal difference.

The attached does the random allocation. If you need another approach happy to help

Allocate Random.yxmd

thelegazy

*Correction, I made a data entry error. Let me retry and see where I end up, sorry about that.

Hey,

Thanks for the help.

However, when I try to apply the solution I get duplicates in my data set.

Also, when I try to add another product in the data set in the example provided it gives me back a number larger than the Quantity Promised for that Product - Country combination.

I've attached the example. Thanks!!

Allocate Random.yxmd

jdunkerley79

I wasnt recomputing the Quantity at the start

I have added this

I have also added the other two picking methods (Largest Decimal Diff, Largest Percentage Diff)

New version attached

Allocate Three Methods.yxmd

thelegazy

Thanks for the update, I didn't get a chance to look at it till today. Thanks!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280