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?
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)
