on 10-28-2006 5:42 AM
Hi experts;
This is a sensible question and it is needed for going live next Monday.
In the InfoCube I have the following data:
Material \ Month Jan Fev Mar Abr
A 10 5 8 4
B 20 15 18 14
C 30 25 28 24
D 40 35 38 34
E 50 45 48 44
TOTAL 150 125 140 120
Using fox formula, how can I calculate a distribution key (using the following formula)?
Distribution key (A, 1) =
value of material A for Jan /Total of materials for Jan
DK(A,1) = 10/150 = 0,066667
This distribution key must be for all the months.
Using variables, I already get the individual value for material:
DATA MONTH TYPE 0FISCPER.
DATA MATERIAL TYPE 0MATERIAL.
{MONTH, MATERIAL, 0AMOUNT}
But how can I get the total of the month for all the materials?
If not fox formula, there is any other way?
Best regards;
Ricardo
Ricardo,
You need two loops on materials within each month. One to calculate the total and another to calculate the distribution key. Try the following.
DATA MONTH TYPE 0FISCPER.
DATA MATERIAL TYPE 0MATERIAL.
DATA DK TYPE F.
DATA TOTALAMT TYPE F.
FOREACH MONTH.
TOTALAMT = 0.
FOREACH MATERIAL.
TOTALAMT = TOTALAMT + {MONTH, MATERIAL, 0AMOUNT}.
ENDFOR.
FOREACH MATERIAL.
DK = {MONTH, MATERIAL, 0AMOUNT} / TOTALAMT.
... further processing using DK ...
ENDFOR.
ENDFOR.
Thanks and regards.
Rajendra
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Rajendra and Ricardo
Does this really work?
I'm trying to do the same, but on year totals (just an horizontal sum for each account). This does not work:
DATA YEAR_TOTAL TYPE F.
DATA Z_ACC TYPE 'ERP/GL_ACCT'.
DATA Z_PERIOD TYPE 0FISCPER3.
FOREACH Z_ACC.
FOREACH Z_PERIOD.
YEAR_TOTAL = YEAR_TOTAL + {'/ERP/AMOUNT', Z_ACC, Z_PERIOD, ZBPC_C01}.
ENDFOR.
ENDFOR.
(I know that I need a sentence to clear YEAR_TOTAL, but my problem begins even before that)
The loops are working in the wrong order. The code is adding all accounts inside one month (making monthly totals). Changing the order of the loops is not a solution. The ABAP code is still taking Account first. Maybe the order of the characteristics inside the InfoCube is the only thing that matters (and maybe Ricardo had luck with that).
Here REFDATA and SELECTION as parameters for the FOREACH loop do not help. If I use a FOREACH Z_PERIOD IN SELECTION to force using the selected account, then the second loop becomes useless, because it loops over a table that has only January records for example (the first loop has already made a Period selection). When the first loop finishes, it clears YEAR_TOTAL, losing the value before taking the next month.
Any idea to solve this?
Thank you!
Sergio
Hello again
I just found out that I need to iterate also inside the other time dimensions that are automatically derivated (generally 0FISCPER if I'm using 0FISCPER3). Otherwise, it will be finishing the loop before finding the other values. Adding this characteristic to the loop Works.
Regards
Sergio
Thanks Rajendra; thats solves the problem.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Do you have data for all the months?
One more solution from that can be used is write a fox to store the distribution factor in a record with no material and period populated for it. Then create another function that will create the ratio for material and store it with material number on the record. (I am talking about one extra keyfigure where you can store the dist %). The third step will be to write function for creating the recrod for material. These 3 steps will be a part of a GPS and attached to the planning folder.
thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.