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.

Excess PO quantity reduction across multiple PO's

JeffVTR
6 - Meteoroid

Hi Gurus

 

I have excess purchase orders in our SAP system by site.

 

I've calculated the total excess PO qty of material by site and now I'm trying to reduce the excess material quantity across multiple purchase order lines by the total amount I've calculated. 

 

JeffVTR_1-1672809038602.png

I've included the last column to represent the expected outcome. 

 

Your thoughts? I've tried searching the forums but not really found what I need. 

 

2 REPLIES 2
ShankerV
17 - Castor

Hi @JeffVTR 

 

Can you please explain the logic to be used to derive the last column shared in the screenshot.

 

Like if Column1 is same, then if we have excess then -1. Like the business logic.

 

JeffVTR
6 - Meteoroid

Hi @ShankerV 

 

Material = SKU of the product, this is vague in the example.

 

The total qty in "Excess PO QTY (Site)" needs to consume the "PO quantity" until the "Excess PO QTY (site)" is Zero. This consumption should only occur respective to "Material" and "Site". 

 

In the example, Material '1' at site 'A' has 3 x Purchase Orders (100001, 100002, and 100003) all with a qty of 3 with total "Excess PO QTY" of -1. This results in the first PO '100001' has 1 removed and equals 2. The other PO quantities remain the same as PO '100001' is not fully consumed.

 

In the example, Material '2' at site 'A' has 2 x Purchase Orders (100004 and 100005) all with a qty of 1 with total "Excess PO QTY" of -2. This results in PO '100004' being reduced to 0, and because -1 is left, PO '100005' is also reduced by 1 resulting in 0. This scenario would keep occurring until the total of "Excess PO QTY" = 0.

 

This is essentially a multi row formula that would reduce the running total of "Excess PO QTY" to 0 for the "Purchase Order" quantities grouped by "Site" and "Material"

 

I hope this helps

Labels