# BAQ Running Inventory Totals with Lag Functions

Hello, I am trying to create a baq that will keep a running total of what is available in inventory.

What I am attempting to do is show a material, the amount of the material that is required for the specified operation, the total amount of that material that is required for all operations (this is organized by a required by date), and then a running inventory of how much material would be available after the current operation. The below image provides an example of what I am attempting.

However the total material required column is not looking at all of the previous records but rather only the previous 1 record.

I know the reason for this is that my calculated field for Total Material Required is a baq utilizing a lag function that only looks back 1 record as can be seen in the image below.

I have tried using a sum function instead of the lag but that also does not provide an accurate total material required.

Does anyone have any advice as to how to get the results I am looking for?

Thank you.

RANGE UNBOUNDED PRECEEDING to define the window frame.

Here is an article: Rows and Range, Preceding and Following - Steve Stedman

1 Like

Why doesnâ€™t sum work? You should be able to make a running total with it.

``````Sum(MaterialQtyRequired) over (partition by JobMtl.HeatNum_C order By OperationDueDate Asc)
``````

That should give you a running sum of the material required. Can you elaborate why itâ€™s not accurate?

Sure, as you can see in the image below while it does calculate a correct total for all 3 it is not doing it for each individual row but giving one total for all 3 rows which unfortunately will not allow me to determine at which operation we would be out of material

Whatâ€™s your due date? If you date is the same, then it wonâ€™t know which is first, so it lumps them together. You need to add another sort in there to make sure each line is unique. Try this (whatever the opSeq field is)

``````Sum(MaterialQtyRequired) over (partition by JobMtl.HeatNum_C order By OperationDueDate, opSeq)
``````

Thank you. Operation due date was the same so I was able to find a field to use.