Hello,
I created a saved search with below
Transaction: Type is any of Invoice, Purchase Order
Transaction: Status is any of Purchase Order: Partially Received, Purchase Order: Pending Receipt, Invoice: Open, Invoice: Paid In Full
On Hand : ROUND(({quantityonhand}),0) Is: 422
On The Way : CASE WHEN {transaction.type} = 'Purchase Order' THEN ({transaction.quantity}-{transaction.quantityshiprecv}) ELSE 0 END Is: 2,000
Last 2 Months Sales : CASE WHEN {transaction.type} = 'Invoice' AND {transaction.trandate} between last_day(add_months({today}, -3)) AND last_day(add_months({today}, -1)) + 1 THEN {transaction.quantity} ELSE 0 END Is: 3,997
Sales Monthly Average : CASE WHEN {transaction.type} = 'Invoice' AND {transaction.trandate} between last_day(add_months({today}, -3)) AND last_day(add_months({today}, -1)) + 1 THEN {transaction.quantity}/2 ELSE 0 END Is: 1,998.5
The issue: I Need To Get The Result For below but I still get ERROR: Invalid Expression
Stock shortage = (On Hand+On The Way)-(Last 2 Months Sales)
(422+2,000)-(3,997)= -1575 /Quantity
({quantityonhand}+CASE WHEN {transaction.type} = 'Purchase Order' THEN ({transaction.quantity}-{transaction.quantityshiprecv}) ELSE 0 END)-(CASE WHEN {transaction.type} = 'Invoice' AND {transaction.trandate} between last_day(add_months({today}, -3)) AND last_day(add_months({today}, -1)) + 1 THEN {transaction.quantity}/2 ELSE 0 END)
Stock term = (On Hand+On The Way)/(Sales Monthly Average)
(422+2,000)/(1,998.5)= 1.2 /Month
({quantityonhand}+CASE WHEN {transaction.type} = 'Purchase Order' THEN ({transaction.quantity}-{transaction.quantityshiprecv}) ELSE 0 END)/(CASE WHEN {transaction.type} = 'Invoice' AND {transaction.trandate} between last_day(add_months({today}, -3)) AND last_day(add_months({today}, -1)) + 1 THEN {transaction.quantity}/2 ELSE 0 END)
So Please Correct The both Formula Bros