Creating Rolling Totals based on product type

colemanv55

Hello,

I am trying to create a total that automatically updates/rolls down when some of the inventory has been exhausted by a sale/order. I need to have the left-over Physical Inventory value roll down to the next truck that will be loading that Rock Color and have that new value used in the calculation.

• In Data Set 1 the value provided by the record “Truck Load Total” under the Load Weight field represents a sum of the bags ordered to be loaded on the truck. Physical Inv. Total represents our current inventory/stocks. We want to exhaust the Truck Load Total value before the Physical Inventory.
• In Data Set 2 we have the Shipment Total Weight for each truck broken out by Rock Color. This value represents the sale/value total we must load on the truck.

I have two starting data sets that are linked by the “Truck Sort Order” and “Truck & Rock Color GroupID” fields. The “Truck & Rock Color GroupID” differs from the “Truck Sort Order” field by creating a unique value based on Rock Color.

EXAMPLE:  The truck GOLIATH has a Shipment Total Weight (Sale) of 3000 RED ROCK. We currently have 2789 RED ROCK ordered and have 22187 in stock. We want to exhaust the value ordered first so, 3000-2789 = 211. We will then subtract that left over value from 22187 to give us 21976. The newfound Physical Inventory of 21976 should appear for the next Physical Inventory of a truck loading RED ROCK. This should continue after each subsequent truck load.

I have attached an example output for clarification.

Thank You! I've been stumped on this one!

Rolling Totals.xlsx

GrowthNatives

Hi @colemanv55 ,
I understand you re trying to create Rolling Totals based on product type, here is how I would go about it:

🗒To Achieve:

You want to simulate rolling inventory depletion by Rock Color, where:

1. Shipment Total Weight is the amount to load on the truck.

2. You first exhaust the Truck Load Total.

3. The remainder comes from the Physical Inventory, which should update (roll down) for each subsequent truck of the same Rock Color.

✅ Proposed Solution in Alteryx

We'll create a rolling calculation grouped by Rock Color, respecting the truck load order.

🧰 Alteryx Workflow Steps 1. Input the Data

• Use the Input Data tool to bring in your two datasets (or just the final combined sheet, if pre-merged).

2. Sort the Data

• Use the Sort Tool to sort by:

  • Rock Color

  • Truck Sort Order (ensuring trucks are loaded in correct sequence).

3. Create a Running Grouped Inventory Logic

To simulate rolling stock updates, we’ll need a Multi-Row Formula Tool that:

• Groups by Rock Color

• Tracks remaining inventory across rows (trucks)
  
  

🧮 Multi-Row Formula Logic

Let’s assume these fields exist:

• [Truck Load Total]

• [Shipment Total Weight]

• [Physical Inv. Total]

Create the following fields:

➤ Field: Remaining Shipment After Truck Load

IF [Shipment Total Weight] > [Truck Load Total]
THEN [Shipment Total Weight] - [Truck Load Total]
ELSE 0

➤ Field: Inventory Used

IF [Shipment Total Weight] > [Truck Load Total]
THEN [Shipment Total Weight] - [Truck Load Total]
ELSE 0

➤ Field: Rolling Inventory

Use a Multi-Row Formula Tool:

• Create Field: Remaining Inventory

• Group By: Rock Color

• Expression:

IF IsNull([Row-1:Remaining Inventory])
THEN [Physical Inv. Total] - [Inventory Used]
ELSE [Row-1:Remaining Inventory] - [Inventory Used]

✅ This ensures inventory rolls down within Rock Color groups, updating as each truck is loaded.

✅ Final Output

You’ll get for each row:

• Shipment Total

• Truck Load Total

• Inventory Used (if any)

• Remaining Inventory (after truck load)

">⭐Hope this solution helps you make the most of Alteryx! If it did, click 'Mark as Solution' to help others find the right answers.

💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!

🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore more]