Complex MOD and accumulate SUM

[Longdaica]

This is a difficult challenge.

I have a list of orders with a certain amount of quantity.  Each order has to go through 2 processes. Each of the process has START WEEK and END WEEK. There is an extra column called "Week required" which is End week - Start Week or each process.

My task is to DISTRIBUTE quantity to each week of each process.

For example:

Order 1 quantity has 10 units

Process 1 start Week 10

Process 1 end week 13

Week required:  3 (13-10)

I have to distribute quantity in a table (week, process 1, process 2).  Rules  of distribution:

1. Even though Start week = 10, end Week 13 but I must distribute quantity EVENLY for  week 10 ,11,12,13 which means week required is actually 4 weeks.

2. How to distribute evenly:

If the quantity is 10, then you get quantity / (Week required +1). 10/4 =2.5 then you somehow round the the 1st three weeks to 3 and the last week will be 1 because the quantity cannot be decimal.  Which mean  you will round up (Week required) and Last week(+1) will have the left over if MOD(number, divisor) =/= 0 . 

The result should be

Week 10: 3

Week 11: 3

Week 12: 3

Week 13: 1

The results will be added  accumulately with the result of different order.

I have sheets:

Sheet 1 (Order sheet) includes: Order, quantity, start week, end week.

Sheet 2 (Quantity distributed) includes: weeks, process 1 , process 2   <= Working sheet

[ponraj]

Hi @Longdaica,  Achieved the difficult challenge...

[vipvailon]

Hi there, I have tested with a single record, your steps are basically correct but something is wrong with the calculation.

This result should give you

Week 11, sum process 1 = 880 / (1+1) = 435

Week 12, sum process 1 = 880 / (1+1) = 435

----------

Week 16, sum process 2= 880 / (2+1) = 293.3333~ = 294

Week 17, sum process 2= 880 / (2+1) = 293.3333~ = 294

Week 18, sum process 2= 880 / (2+1) = 293.3333~ = 292

I think we have a miscommunication here. Almost there, please help! The process 1 are independent with process 2, therefore quantity are no shared. Just like you are washing 10 dishes, then you still have to dry 10 dishes.

(p.s I am the OP, just log into the wrong account)

[ponraj · Accepted solution]

Uploading the workflow after making process 1 and process 2 independent.   

[Longdaica]

Hey Man

Why did you have 2 union at the start? it doesnt do anything in my opinion ... please correct me 

[ponraj · Accepted solution]

Yes. Union is not required at the start. I used union tool for adding the week ranges from process 1 and process 2 on my first solution I shared. Forgot to remove union tool while redesigning the workflow to make processes independent as I copied parts of workflow from solution 1 I provided. So, you can remove the two union tool at the start.

Regards,
Ponraj S