on 01-14-2013 7:37 PM
I am trying to come up with a simple inventory report but simple calculations are not working like they should. I’m hoping someone here can show me what I’m doing wrong.
For each product there is a starting amount (Available), a monthly demand (Demand) and periodic expiration (Expire). However the calculations are not working as they should.
Trans Date | Available Qty | Expire Qty | Demand | Running On Hand | Running Demand | Actual Expire | Starting Amount | Deduct | Running Avail |
1/1/2013 | 304 |
|
| 304 | 0 | 0 |
| 0 | 304 |
2/1/2013 |
|
| 3 | 304 | 3 | 0 | 304 | 3 | 301 |
3/1/2013 |
|
| 4 | 304 | 7 | 0 | 301 | 4 | 297 |
4/1/2013 |
|
| 4 | 304 | 11 | 0 | 297 | 4 | 293 |
5/1/2013 |
|
| 6 | 304 | 17 | 0 | 293 | 6 | 287 |
6/1/2013 |
| 18 | 6 | 304 | 6 | 1 | 287 | 7 | 281 |
7/1/2013 |
| 109 | 6 | 304 | 6 | 103 | 281 | 109 | 172 |
8/1/2013 |
|
| 7 | 304 | 13 | 0 | 172 | 7 | 165 |
9/1/2013 |
|
| 5 | 304 | 18 | 0 | 165 | 5 | 160 |
10/1/2013 |
| 177 | 8 | 304 | 8 | 159 | 160 | 167 | 145 |
11/1/2013 |
|
| 17 | 304 | 25 | 0 | 145 | 17 | 128 |
12/1/2013 |
|
| 19 | 304 | 44 | 0 | 128 | 19 | 109 |
For example from the table above - On 6/1/2013 - 18 are due to expire. However before that date we used 17 (previous(running demand)) so only 1 will actually expire (Actual Expire). In addition Six more are due to be used on 6/1 (Demand) for a total deduction of 7 (Deduct). But when I subtract this from the Starting amount (which is just Previous(Running Avail)) I get 281 instead of 280.
The variable formulas are :
[Running Demand] = If [Expire].[Available Qty]>0 Then 0+[Util Demand].[Available Qty] Else 0+Previous(Self)+[Util Demand].[Available Qty]
[Actual Expire] =If [Expire].[Available Qty]>0 Then ( If [Expire].[Available Qty]-Previous([Running Demand]) >0 Then ([Expire].[Available Qty]-Previous([Running Demand]) )Else 0) Else 0
[Deduct] =0+[Util Demand].[Available Qty]+[Actual Expire]
[Running Avail] =0+Previous(Self) +[On hand].[Available Qty] - [Deduct]
Thanks,
John
Hi John,
it seems that you need to use calculation contexts as per "Using Functions Formulas and Calculations" guide, http://help.sap.com/boall_en
Thanks
Simone
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.