Average Inventory Level

[Former Member]

I want to calculate to average inventory level for any given item on SAP B1. There are several reports that show how many items are available in stock at any given moment, and I could calculate manually the average inventory level, but I was wondering if it is possible to create a report and have that information automatically calculated.

Example: let's assume the Item 1 had the following inventory data:

Day 1 - 400 units
Day 2 - 405 units
Day 3 - 500 units
Day 4 - 100 units
Day 5 - 50 units
Day 6 - 250 units
Day 7 - 180 units

Average inventory level = (400+405+500+100+50+250+180) ÷ 7 = 269.2 units for this given period.

I want then to calculate for larger periods, for example one year.

I'm using SAP Business One Version 9.1 (9.10.150)

Thank you

[Former Member]

Hi Alex,

You should take a query like this one, that calculate the balance and dived by the quantity of days that you choose.

select (sum(InQty) - sum(OutQty))/ DATEDIFF(day,'20160101', '20161231' ) as avg,  
(sum(InQty) - sum(OutQty)) as balance,
ItemCode
from OINM 
where DocDate >= '20160101'
and DocDate <= '20161231'
and ItemCode = '00087'
group by ItemCode