GI SUM of Days without Stock

  • 3 August 2023
  • 1 reply

Userlevel 6
Badge +4

I’m looking to create a GI with a item list and and a sum of how many days this item was at 0 qty in the warehouse in the past 365 days. 


Where do I start? 

I thought maybe using the INItemSiteHistDay table would be the way to go but I’m just not sure where to go from there.


Best answer by lauraj46 3 August 2023, 22:14

View original

1 reply

Userlevel 7
Badge +7

Hi @Jeff96 ,

You could the DateInfo table as a starting table to get the 365 dates.  The tricky bit after that is to get the total item quantity by warehouse (possibly with multiple locations) for any particular date.

The INItemSiteHistDay table only contains records for the dates where there is a change. 

Maybe someone else knows of a different DAC that already has the daily quantities.  In that case you should be able to do this in a GI.  Otherwise, I think would be easier and more efficient in a report. There you can sort the InItemSiteHistDay records by date and use variables to do some calculations.

A basic design might be something like this:

  • Cross join DateInfo with INItemSiteHistDay
  • Group by [InItemSiteHistDay.LocationID] and sort by [INItemSiteHistDay.sdate].
  • Create variables for the DailyLocQty and the DailyWHQty. 
  • In the detail section - if sdate is prior to DateInfo.Date, set the DailyLocQty = EndQty.  Since the records are sorted by date this will get you the last EndQty for that location. 
  • In the footer for the locationid - add the DailyLocQty to the DailyWHQty
  • In the footer for [DateInfo.Date] = if the DailyWHQty = 0 add 1 to the count

Hope this give you some ideas!




About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved