Working on a shipping report that needs Net on Hand by item that recalculates in Analyzer

TripleZ

In the analyzer table, the user will sort by different columns and I need the Net on hand to adjust based on the order chosen for each part in the set. For instance, if they are looking at it by customer name, or by order number, the net on hand for those items needs to dynamically change so that Net On Hand is reduced in the order viewed.

Thanks,

MarkSnodgrass

The window function itself

SUM(SUM(`qtyOrdered`)) OVER (PARTITION BY `OrderNumber` ORDER BY `CustomerName`)

should be producing a running total of the qty ordered. To troubleshoot, try just having this as a beast mode and put it in your table. If it is working correctly, according to most recent result, your first row should show 24 and the 2nd row should show 36. See if you can get that to calculate correctly and then move towards subtracting from the total on hand.

MarkSnodgrass

Can you provide some sample data or screenshot of how you want it to look? It's difficult to propose a solution without some additional information.

TripleZ

Here is the sample data for one part, but the report hold multiple parts.

Order Cust Name Item ShipDate QtyOrdered QtyShipped TotalOH NetOH

1234 Acme 1 0335432 2023-10-18 50 0 200 150

3334 ABC Building 0335432 2023-10-18 30 0 200 120

9876 Amazon 0335432 2023-10-18 40 10 200 90

can be sorted by Cust Name or order number and the netOH should adjust to the order viewed in. so if sorted by Cust Name it would display as below:

Order Cust Name Item ShipDate QtyOrdered QtyShipped TotalOH NetOH

3334 ABC Building 0335432 2023-10-18 30 0 200 170

1234 Acme 1 0335432 2023-10-18 50 0 200 120

9876 Amazon 0335432 2023-10-18 40 10 200 90

MarkSnodgrass

You are going to need to use a window function in beast mode to do this. This feature isn't on by default, so ask your CSM to turn it on if it isn't on already. Once it is on, you would need to do something like this for your calculation for Net On Hand



TotalOnHand + QtyShipped - SUM(SUM(`qtyOrdered`)) OVER (PARTITION BY `OrderNumber` ORDER BY `CustomerName`)

TripleZ

Hmm.. I can't seem to make that work. It still calculates the NetOH using just the qty ordered - qty shipped from that line, then recalculates on the next occurrence of the part number. So results look like this:

customer item Qtyordered - qtyshipped Total OH net OH

Acme 034662 24 42 18

Acme 034662 12 42 30

MarkSnodgrass

The window function itself

SUM(SUM(`qtyOrdered`)) OVER (PARTITION BY `OrderNumber` ORDER BY `CustomerName`)

should be producing a running total of the qty ordered. To troubleshoot, try just having this as a beast mode and put it in your table. If it is working correctly, according to most recent result, your first row should show 24 and the 2nd row should show 36. See if you can get that to calculate correctly and then move towards subtracting from the total on hand.