Inventory status calculations

[Anonymous]

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

[Anonymous]

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.

[sunny_talwar]

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....