# Trying to do a lag function to calculate if a specific order is fulfilling back order or inventory

Options
Member
edited July 3

Hello,

Please see below on what I am trying to do. I have open orders that are ranked based on earliest ship date and the ORDQTY. I am looking at the total sales QTY for that model and trying to do a lag calculation to see if a specific line item is fulfilling the outstanding sales qty or going into a negative number which would mean building inventory.

Basically line item 1 would be (112-8) which is 104. The next line item would be (104-10) which would be 94…and so on.

Thanks

• Coach
Options

Perhaps you can try a combination of Beast Mode calculations and the window functions? The goal is to calculate the cumulative outstanding quantity and determine if the line item is fulfilling the outstanding sales quantity or going into negative (building inventory).

Running Total Order Quantity

```SUM(Ord Qty) OVER (PARTITION BY Model ORDER BY Ship Date)
```

Outstanding

```Sales Qty - SUM(Ord Qty) OVER (PARTITION BY Model ORDER BY Ship Date)
```

Sort the data by Ship Date and Order Quantity for a running total.

** Did this solve your problem? Accept it as a solution! **

• Coach
Options

Perhaps you can try a combination of Beast Mode calculations and the window functions? The goal is to calculate the cumulative outstanding quantity and determine if the line item is fulfilling the outstanding sales quantity or going into negative (building inventory).

Running Total Order Quantity

```SUM(Ord Qty) OVER (PARTITION BY Model ORDER BY Ship Date)
```

Outstanding

```Sales Qty - SUM(Ord Qty) OVER (PARTITION BY Model ORDER BY Ship Date)
```

Sort the data by Ship Date and Order Quantity for a running total.