Days On Hand

[jholmes]

I am trying to create a query that will extract our current on-hand totals and calculate the "days on hand" for each item based on the previous 6-months of transactions (Job Issues, CO Shipments, TO Shipments).

This would seem to be a common metric so I was wondering if anyone else had already created a similar query?

I am starting with the following fields:
item.item
item.description
item.u-m
Ext-Std (item.unit-cost * itemwhse.qty-on-hand)
itemwhse.qty-reorder (just for comparison)
itemwhse.qty-on-hand
itemwhse.alloc-trn
itemwhse.qty-alloc-co
itemwhse.qty-wip
itemwhse.qty-ordered
itemwhse.qty-trans
I-Qty (IF (matltran.trans-type = "I") THEN (matltran.qty) ELSE (0))
S-Qty (IF (matltran.trans-type = "S") THEN (matltran.qty) ELSE (0))
T-Qty (IF (((matltran.trans-type = "T") AND (matltran.loc <> "TRANSIT"))) THEN (matltran.qty) ELSE (0))
matltran.trans-type

The TO Shipments are tricky because I only want to see Outbound transactions not inbound transactions. I haven't gotten to the actual calculation of "days on hand". If anyone has already performed a similar query it might save me a lot of head scratching.

 

[ZombieBritts]

I am trying to create a query that will extract our current on-hand totals and calculate the "days on hand" for each item based on the previous 6-months of transactions (Job Issues, CO Shipments, TO Shipments).

This would seem to be a common metric so I was wondering if anyone else had already created a similar query?

I am starting with the following fields:
item.item
item.description
item.u-m
Ext-Std (item.unit-cost * itemwhse.qty-on-hand)
itemwhse.qty-reorder (just for comparison)
itemwhse.qty-on-hand
itemwhse.alloc-trn
itemwhse.qty-alloc-co
itemwhse.qty-wip
itemwhse.qty-ordered
itemwhse.qty-trans
I-Qty (IF (matltran.trans-type = "I") THEN (matltran.qty) ELSE (0))
S-Qty (IF (matltran.trans-type = "S") THEN (matltran.qty) ELSE (0))
T-Qty (IF (((matltran.trans-type = "T") AND (matltran.loc <> "TRANSIT"))) THEN (matltran.qty) ELSE (0))
matltran.trans-type

The TO Shipments are tricky because I only want to see Outbound transactions not inbound transactions. I haven't gotten to the actual calculation of "days on hand". If anyone has already performed a similar query it might save me a lot of head scratching.

OK .. I am a MFGPRO person , but it would make sense to calculate days on hand based on 6 months of SALES History for an item , not item transaction history.

Good Luck - hope this helps.

Glen