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.
Solved! Go to 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
Thanks Roland that worked. Cheers Karl