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

Options

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,

Best Answer

  • MarkSnodgrass
    Answer ✓
    Options

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    Options

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • TripleZ
    Options

    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
    Options

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • TripleZ
    Options

    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
    Answer ✓
    Options

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.