topic Re: Inventory status calculations in App Development

Inventory status calculations

Anonymous — Wed, 26 Apr 2017 15:35:22 GMT

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

sunny_talwar — Wed, 26 Apr 2017 15:50:36 GMT

May be try this

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

Re: Inventory status calculations

Anonymous — Wed, 26 Apr 2017 16:01:32 GMT

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

sunny_talwar — Wed, 26 Apr 2017 16:03:51 GMT

May be try this

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

Re: Inventory status calculations

Anonymous — Wed, 26 Apr 2017 16:16:53 GMT

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

sunny_talwar — Wed, 26 Apr 2017 16:22:45 GMT

Try this

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

Re: Inventory status calculations

Anonymous — Wed, 26 Apr 2017 16:26:46 GMT

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

Anonymous — Thu, 27 Apr 2017 08:15:30 GMT

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

sunny_talwar — Thu, 27 Apr 2017 09:02:18 GMT

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

Re: Inventory status calculations

sunny_talwar — Thu, 27 Apr 2017 09:19:03 GMT

Attaching a sample with this approach

Re: Inventory status calculations

Anonymous — Fri, 28 Apr 2017 09:44:29 GMT

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

sunny_talwar — Fri, 28 Apr 2017 11:13:26 GMT

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