11 Replies Latest reply: Apr 28, 2017 7:13 AM by Sunny Talwar

# Inventory status calculations

Hi,

I have an app where I need to calculate inventorystatus per day, per product, per store.  So we have an beginning inventory status and then transaction on top of that gives the current status of the inventory, so sold items are subtracted from the inventory and purchases added.

So I would like to create a column either during load or via set analysis that calculates the inventorystatus. I am having some problems with this I tried to work with something like rangesum(above(Sum(Qty),0,RowNo(Total))), but this works in someways but when I productno to it it looses track.

So any Idea on how to solve this?

 PostingDate StoreNo ProductNo EntryType Qty TotalInvQty 28.2.2017 1 1 Counted 1000 1000 28.2.2017 2 1 Counted 100 100 28.2.2017 1 2 Counted 300 300 28.2.2017 2 2 Counted 1000 1000 1.3.2017 1 1 Sales -100 900 1.3.2017 2 1 Purchase 400 500 2.3.2017 1 2 Purchase 200 500 3.3.2017 2 2 Sales -100 900 3.3.2017 1 1 Sales -50 850 4.3.2017 2 2 Sales -50 850

Regards

Birkir

• ###### Re: Inventory status calculations

May be try this

Aggr(RangeSum(Above(Sum(Qty), 0, RowNo())), StoreNo, ProductNo, PostingDate)

• ###### Re: Inventory status calculations

Hi Sunny,

Yes this works for i a way. I was hoping to find a solution that works even if I take out one of the dimensions, for example I want to get a total number of the inventory, or a total number of the inventory per product not store.

Regards

Birkir

• ###### Re: Inventory status calculations

May be try this

Sum(Aggr(RangeSum(Above(Sum(Qty), 0, RowNo())), StoreNo, ProductNo, PostingDate))

• ###### Re: Inventory status calculations

This is getting very close solving it, one thing is that if I select in the table one date the calculation will only show the transaction for that day but not the status of the inventory

• ###### Re: Inventory status calculations

Try this

Sum(Aggr(RangeSum(Above(Sum({<PostingDate>}Qty), 0, RowNo())), StoreNo, ProductNo, PostingDate)) * Avg(1)

• ###### Re: Inventory status calculations

Thank you so much Sunny excellent work, this solves it.

I will mark it as the correct answer

Regards

Birkir

Thanks again for all your help.

• ###### Re: Inventory status calculations

Hi all,

So this definently works but is very slow when all data has been loaded. I think the only way to solve this is to do it in the load script.

Does anyone have any hints ?

• ###### Re: Inventory status calculations

You can use The As-Of Table in that case

• ###### Re: Inventory status calculations

Attaching a sample with this approach

• ###### Re: Inventory status calculations

Yes this works perfectly as well. I was also thinking of trying peek() and previous(). I dont know if that could be possible or not. But might be worth the try as a 3rd method.

• ###### Re: Inventory status calculations

You can use that, but using peek/previous your numbers will be static and won't change based on selections unless you use peek/previous at the most granular level.... I wouldn't recommend going that route unless you really have to....