Monthly Inventory report based on SAP data

[#1]

Hi

I am trying to build monthly inventory report from the SAP. There is "current stock" in SAP and all inventory movements from history. I would need to build a logic in expression (in expression I think is easiest?) that would calculate monthly stock levels based on these inputs. Any ideas how to write that?

I have attached simple example of situation. I have there column "Stock" in where I would like to see Month end inventories. In this example I have values 5500, 9000, 7000, 8000 which I would like to see under month 5 because it is month end inventory of that month. In a same way I want to see figures 4000, 7000, 5000 & 8000 as month end inventoy (in column stock) in period 4.

I hope I was clear with my problem.

Thank you very much already beforehand!

Regards

Janne

[Anonymous]

You can:

1. Set up a date island that contains combinations of all the dates and relevant dimensions (e.g. ProductID)

2. Set up an AccumulationKey alongside you Movements data, which you can test for to see if the month or date has changed the data

3. Load the Movement data into the date island, in reverse order, testing for whether the Accumulation key has changed. If it has (and a new month or date is being loaded) then you can load in the previous balance minus the sum of the movements

See this excellent example: http://community.qlik.com/message/100042#100042

If does the accumulation going forwards in time, but can be changed to go backwards in time.

Jonathan

[#3]

Thanks!

I think this might be excatly what I was looking

Regards

Janne

[#4]

Hi,

I have a problem with this logic. Obviously I am doing something wrong, but I can't see where.

I adapted your logic, but for some reason I can't get anything into monthly opening balance.

Below is the script I am using in inventory accumulation calculation. I have also attached a picture from table view, in where you may see my problem

The script:

InventoryAccumulations:

LOAD distinct

AccumulationKey,

MonthlyMovementKG,

if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), 0, peek(ClosingAmount)) AS OpeningAmount,     

if(rowno() = 1 OR AccumulationKey <> peek(AccumulationKey), MonthlyMovementKG, peek(ClosingAmount) + MonthlyMovementKG) AS ClosingAmount,

Plant,

Material_MATNR,

MoveYear,

MoveMonth,



date(makedate(MoveYear, MoveMonth), 'MM-YY') AS InvPeriod

RESIDENT

InventoryAccumulationsTemp;

Many thanks already beforehand!

Regards

Janne