cancel
Showing results for
Did you mean:
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
MVP

Try this

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

11 Replies
MVP

May be try this

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

Anonymous
Not applicable
Author

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

MVP

May be try this

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

Anonymous
Not applicable
Author

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

MVP

Try this

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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 ?

MVP

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

MVP

Attaching a sample with this approach