Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Complex MOD and accumulate SUM

Longdaica
7 - Meteor

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

5 REPLIES 5
ponraj
13 - Pulsar

Hi @Longdaica,  Achieved the difficult challenge...

 

Workflow.PNG

Results.PNG

vipvailon
5 - Atom

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

 

Untitled.pngUntitled1.png

 

 

 

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
13 - Pulsar

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

Workflow.PNGResults.PNG

Longdaica
7 - Meteor

Hey Man

 

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

ponraj
13 - Pulsar
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
Labels