Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Rounding then Evenly Distributing

thelegazy
7 - Meteor

Hi All, 

 

I have bit of a complex solution I'm trying to work on, at least for me. I've attached an excel sheet with the proof of concept. Any help would be greatly appreciated!

 

The scenario is as such: 

 

  • We can have 150 units of Product 1(Cell G1)
  • The product is offered in sizes: S-XL
  • The size ratio we maintain is for sizes XS-XXXL
  • I  want to lookup the Size Ratio for the Sizes Offered (S-XL)
    • However, once you add up the ratios you fall below 100% since we aren't offered all the sizes
  • I want to distribute the remaining % amongst the Offered Sizes (in the excel example it's 35%)
  • So it would be the initial Size Ratio + Difference amount
  • Then multiply each size by the Quantity Offered (150 Units) to get the quantities by size
    • This needs to be whole numbers as I can't purchase .5 of a unit
  • Another crease is that I need the total to equal the Quantity Offered of 150 units, essentially the ceiling and floor.
    • In my current process, due to rounding it's often times below. 
  • When the Product is offered in fewer sizes than what the curve is for, the total 

I have tried to do the most I can, but I'm not as advanced to complete this logic. 

 

Any help or pointing in the right direction would be greatly appreciated!

 

3 REPLIES 3
jdunkerley79
ACE Emeritus
ACE Emeritus

Try the attached.

 

It starts by selecting the percentage allocation for the available sizes

It then computes the nearest integer below for each size based on total allocated percentage and availabile qty.

You then know you will be within 1 of the ideal allocation for each size, so calculates the residual (r) and then adds 1 to the first r rows ordered by size of the fractional allocation.

 

This will mean that qty will be right and allocation will be fairly close to target percentage.

 

thelegazy
7 - Meteor

You sir are a gentleman and a scholar. 

 

I thought this was gonna take me forever to figure out, managed to implement it in one day. Thank you so much!!

collin_pace
9 - Comet

Thanks @jdunkerley79.  A few years down the road from this posting and your solution helped me out this morning.  Much appreciated.

Labels
Top Solution Authors