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

[sunny_talwar]

May be try this

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

[Anonymous]

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

[sunny_talwar]

May be try this

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

Try this

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

[Anonymous]

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.

[Anonymous]

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 ?

[sunny_talwar]

You can use The As-Of Table‌ in that case

[sunny_talwar]

Attaching a sample with this approach