The current logic in my query s only picking up the “QUANTITY RESERVED”

[Karl_Spratt]

Hi Community 

Need to help please 

The current logic in my query (which has lots of inputs)  is only picking up the “QUANTITY RESERVED” from the Consolidated Backlog report as the “Past Due Demand”.  This is not giving the planners an accurate picture of the backlog when they have a stock out, as if we don’t have inventory available to reserve then the actual past due CRD backlog is not being reflected in the report.  Our number of Stockouts is growing, so it will really help if the  report shows the full backlog picture.

Can you advise he Logic in the Alteryx query to reflect below data in the “Past Due Demand” column.

Current Logic:  “Past Due Demand” equals sum of “QUANTITY RESERVED” in the Consolidated backlog report.

New logic:

Past Due Demand” equals sum of “QUANTITY RESERVED”

plus IF “RD Status” equals “Past Due” and “QUANTITY RESERVED” is zero, then include sum of “ORDERED QUANTITY”

Attaching a sample of the Consolidated backlog report we'd use in production. 

[RolandSchubert · Accepted solution]

Hi @Karl_Spratt ,

I think, the new logic you defined is very close to the solution ... add a Formula tool to calculate "Past Due Demand" using the formula:

IF([QUANTITY RESERVED] = 0 AND [RD STATUS] = "Past Due") THEN
      [ORDERED QUANTITY]
ELSE
      [QUANTITY RESERVED]
ENDIF 

There are two  cases:
(1): Quantity Reserved = 0 AND RD Status = "Past Due" -> Past Due Demand = Ordered Quantity (not necessary to add Quantity Reserved, because this is 0!

(2): all other situations -> Past Due Demand = Quantity Reserved

Based on the result of this calculation, you can use a Summarize tool to aggregate by e.g. Item or whatever is needed.

Hope this is helpful.

Best,

Roland

[Karl_Spratt]

Thanks Roland that worked. Cheers Karl