I have a summary transaction search for sales orders over a month which groups by item and sums quantity. I want to put in the search the sum of the location on hand for 3 of our inventory locations added together. when I do a sum of
I am adding three locations together with a case when statement case when {item.inventorylocation}=LOC1' then {item.locationquantityonhand} else 0 end + case when {item.inventorylocation}=LOC12' then {item.locationquantityonhand} else 0 end + case when {item.inventorylocation}=LOC3' then {item.locationquantityonhand} else 0 end
I have tried selecting the summary type of sum and maximum
When I do a sum, it overstates because it is adding for each sales order line found.
When I do a maximum, it understates because it brings back only one location, that with the highest on hand inventory.
I tried a partition by/partition over, but the search blew up over the record count when I started with the item table and moved to transactions.
How do I show the on hand for loc 1 plus loc 2 plus loc 3?