How to find history of inventory from current inventory?

[Anonymous]

I am working on an inventory project which requires me to find the historical inventory over the past several years. I have multiple scripts which I believe will help me calculate the histoy of inventory, however, I'm not sure how to combine them in the script to get the history. In general, I have calcualted the history of the change in inventory (by the day), by adding work order and transfers and subtracting invoices. The resulting daily inventory change is called[Inventory Change]. I also have a value for the current invenotry in the warehouses right now denoted as [Total Current Inventory]. What I must do is now work backwards (from today) adding or subtracting the Inventory Change from the previous day. My hypothesis is that by doing this, I will find a pretty accurate history of the inventory. I am at a complete loss, however, as to how to go about doing this. I also have a CurrentDate field which gives the today whenever I reload the script thru the ODBC connection. ANy feedback on  how to calcualte my invenotry or an a more direct approach to finding the history would be greatly appreciated!

Here is my script:

////////////////////////////////////////////////
// F4111 Loading Item Ledger Info
////////////////////////////////////////////////
ItemLedger:
LOAD
     ILDCT as [Document Type],
     ILMCU as [Business Unit],
     ILDOCO as [Order Number],
     If(IsNum(ILLITM), ILLITM) As [Item Number],
     Floor(ILTRQT/10000) as [Quantity],
     If(ILDCT = 'RI', ILTRQT/10000) as [Invoice Key],
     If(ILDCT = 'IC', ILTRQT/10000) as [Work Order Key],
     If(ILDCT= 'OV', ILTRQT/10000) as [Transfer Key],
     If(Len(text(ILTRDJ))>5,Date(MakeDate(1900 + (left(text(ILTRDJ),1) * 100) + mid(text(ILTRDJ),2,2)) + (right(text(ILTRDJ),3) - 1)),if(ILTRDJ > 0 ,Date(MakeDate(1000 + (left(text(ILTRDJ),1) * 100) + mid(text(ILTRDJ),1,2)) + (right(+text(ILTRDJ),3) - 1)), Null())) as [Order Date]
;SQL SELECT *
FROM "JDE_PRODUCTION".PRODDTA.F4111

////////////////////////////////////////////////
//Date and Calendar Info
////////////////////////////////////////////////
DateInfo:
Load DISTINCT
     [Order Date],
     Year([Order Date]) as Year,
     Month([Order Date]) as Month,
     WeekEnd([Order Date]) as [Week End],
     'Q' & Ceil(Month(num([Order Date]) + Right([Order Date], 3) - 1)/3) as QuarterResident ItemLedger;

CurrentDateTemp:Load
     Date(Max([Order Date]), 'M/D/YYYY') as CurrentDateResident ItemLedger;
Let vMaxVar = Peek('CurrentDate');

////////////////////////////////////////////////
//Step 1 Inventory Calc
////////////////////////////////////////////////
InventoryTemp:
Load Distinct
     [Item Number],
     Sum([Invoice Key]) as Invoice,
     Sum([Work Order Key]) as [Work Order],
     Sum([Transfer Key]) as TransfersResident ItemLedger
     Group By
           [Item Number];
//////////////////////////////////////////////////
//// Inventory Info
//////////////////////////////////////////////////
InventoryInfo:
Load Distinct
     [Item Number],
     Sum(Invoice+[Work Order]+Transfers) as [Inventory Change]

Resident InventoryTemp
     Group By
     [Item Number];
DROP Table InventoryTemp;
////////////////////////////////////////////////
//Current Inventory Info
////////////////////////////////////////////////
ItemLocationFile:
LOAD
     LIITM as [Item Number (Short)],
     LIMCU as [Business Unit],
     LILOCN as Location,
     LIGLPT as [G/L Category],
     LIPQOH/10000 As [Qty On Hand]
;SQL SELECT
     LIITM, LILOCN, LIGLPT, LIPQOH, LIMCU
FROM "JDE_PRODUCTION".PRODDTA.F41021
where (LIPQOH <> 0);

////////////////////////////////////////////////
//Current Inventory Final
////////////////////////////////////////////////
CurrentInv:
Load Distinct
     [Item Number (Short)],
     Sum([Qty On Hand]) as [Total Current Inventory]

Resident ItemLocationFile
     Group By
           [Item Number (Short)];
          
Drop Table ItemLocationFile;

[Anonymous]

Any responses would be greatly appriciated. I'm at a complete loss.

[#3]

Did you ever have any luck?

[Anonymous]

Hi, not as of yet. I've tried what feels like a million things and all of them have been inaccurate. I have made a couple of changes and now I have an accurate [Inventory Change] and [Current Inventory]. What I need to do is take the current inventory and add/subtract the inventory change for each day backwards 3 years. Right now I am using the expression:

Sum([Current Inventory])+Sum([Inventory Change])

But this simply adds/subtracts the daily inventory change to today's current inventory so it is not accurate. What i need to do is take the current inventory today and add/subtract the inventory change from yesterday to get yesterdays inventory. Then continue the process by add/subtracting the previous days inventory change to yesterdays inventory  to find the day befores inventory. I must continue this process backwards for 3 years.

I have tried an ABOVE expression but have not found anything useful.

Any ideas?

Thanks for any responses!

[#5]

Row and column expressions may work. Do you have a sample file?

[Anonymous]

Yes. I attached the file to the original post.

[Anonymous]

What exactly are row and column expressions?

[michael123 · Accepted solution]

Hi,

Made a test with rangesum. Maybe it can be of use for you moving forward. Please check new sheet and expression under the green column.

Most items seems to be correctly calculated but there are some issues which i haven't figured out if they depend on data model, negative inventories or the expression it self. You will also need some set analysis if you want to be able to select time dimensions.

Regards

/Michael