Skip to Content
0

Average Inventory Level

Mar 22, 2017 at 10:40 AM

60

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Lais Meuchi Mar 27, 2017 at 07:09 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded