Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am facing difficulty while performing a recursive calculation to obtain daily closing stock,
The problem is as follows, I have opening stock balance for day 1, daily sales qty, order qty(EOQ) and Safety stock qty, using which I have to obatin the closing stock qty for each day.
to calculate closing stock I check first
if opening stock>safety stock then opening stock -daily sales
else
opening-daily sales+EOQ
endif
form day 2 onwards the opening stock value will be the closing stock calculated for day 1, i.e. to calculate closing stock of any days other than day 1, previous day's closing stock must be used as opening stock.
See table 1
I have tried using above function but since it works on existing fields only, it did not gave any solution
Day | Opening Stock | Daily Sales | EOQ | Safety Stock | Closing Stock |
1 | 90 | 10 | 30 | 70 | 80 |
2 | 90 | 10 | 30 | 70 | 70 |
3 | 90 | 10 | 30 | 70 | 100 |
4 | 90 | 10 | 30 | 70 | 90 |
5 | 90 | 10 | 30 | 70 | 80 |
6 | 90 | 10 | 30 | 70 | 70 |
7 | 90 | 10 | 30 | 70 | 100 |
I think, for third row you should have a closing stock of 90 because your starting is 70 and the daily sales is 10... making is 60 and plus 30 = 90
May be try these expressions
Opening Stock
=Alt(Above(If(RangeSum(Column(1), -[Daily Sales]) >= [Safety Stock], RangeSum(Column(1), -[Daily Sales]), RangeSum(Column(1), -[Daily Sales], EOQ))), [Opening Stock])
Closing Stock
=If(RangeSum(Column(1), -[Daily Sales]) >= [Safety Stock], RangeSum(Column(1), -[Daily Sales]), RangeSum(Column(1), -[Daily Sales], EOQ))
I think, for third row you should have a closing stock of 90 because your starting is 70 and the daily sales is 10... making is 60 and plus 30 = 90
May be try these expressions
Opening Stock
=Alt(Above(If(RangeSum(Column(1), -[Daily Sales]) >= [Safety Stock], RangeSum(Column(1), -[Daily Sales]), RangeSum(Column(1), -[Daily Sales], EOQ))), [Opening Stock])
Closing Stock
=If(RangeSum(Column(1), -[Daily Sales]) >= [Safety Stock], RangeSum(Column(1), -[Daily Sales]), RangeSum(Column(1), -[Daily Sales], EOQ))
thank you very much , this worked exactly how I wanted, Also I want to show the average of these closing stock in a KPI would that be possible.