Calculation within records

[Former Member]

Hi All,

I have data for Material,Week and quantity contained in internal table as below. I have a forth column which is blank in internal table. I have to calculate average value in the column based upon all values for same material considering all weeks remaining below that row. Say if Material M1 has 10 weeks data then column "Calculated Column" in first row should show "Sum of 10 weeks quantity/10", in second row "Sum of 9 weeks/9" and so on.

Material	Week	Quantity	Calculated Column
M1	1	10	(10+11+12)/3
M1	2	11	(11+12)/2
M1	3	12	12
M2	1	20	(20+21+22)/3
M2	2	21	(21+22)/3
M2	3	22	22

Could you please give me some better ideas to handle this in the loop?

Cheers,

-Su

[Former Member]

Hi All,

I have data for Material,Week and quantity contained in internal table as below. I have a forth column which is blank in internal table. I have to calculate average value in the column based upon all values for same material considering all weeks remaining below that row. Say if Material M1 has 10 weeks data then column "Calculated Column" in first row should show "Sum of 10 weeks quantity/10", in second row "Sum of 9 weeks/9" and so on.

Material	Week	Quantity	Calculated Column
M1	1	10	(10+11+12)/3
M1	2	11	(11+12)/2
M1	3	12	12
M2	1	20	(20+21+22)/3
M2	2	21	(21+22)/3
M2	3	22	22

Could you please give me some better ideas to handle this in the loop?

Cheers,

-Su

[davis_raja]

Dear Kaur,

Sort your internal table by material ascending and week descending.

Then have a count of the week for every loop and have a local variable to add up the quantity and calculate the value.

Try the code provided below.

SORT it_tab BY zmatnr ASCENDING zweek DESCENDING.

LOOP AT it_tab INTO wa_tab.

   AT NEW zmatnr.
     CLEAR : v_week_count, v_qty.
   ENDAT.

   v_week_count = v_week_count + 1.
   v_qty        = v_qty + wa_tab-zqty.

   wa_tab-zcalc_col = v_qty / v_week_count.

   MODIFY it_tab FROM wa_tab TRANSPORTING zcalc_col.

ENDLOOP.

[VenkatRamesh_V]

Hi Kaur,

Hope it helpful.

DATA: BEGIN OF itab OCCURS 0,
       material(02) TYPE c,
       week TYPE i,
       qty  TYPE i,
       cal  TYPE i,
       flag(01) TYPE c,
       END OF itab,

       itab1 LIKE itab OCCURS 0 WITH HEADER LINE ,
       lv_qty TYPE i,
       wa LIKE LINE OF itab,
       lv_week TYPE i.



FIELD-SYMBOLS: <wa> like LINE OF itab,
                <wa1> like LINE OF itab.

START-OF-SELECTION.

itab-material = 'M1'.
itab-week     = '1'.
itab-qty      = '10'. APPEND itab. CLEAR itab.


itab-material = 'M1'.
itab-week     = '2'.
itab-qty      = '11'. APPEND itab. CLEAR itab.

itab-material = 'M1'.
itab-week     = '3'.
itab-qty      = '12'. APPEND itab. CLEAR itab.



itab-material = 'M2'.
itab-week     = '1'.
itab-qty      = '20'. APPEND itab. CLEAR itab.

itab-material = 'M2'.
itab-week     = '2'.
itab-qty      = '21'. APPEND itab. CLEAR itab.

itab-material = 'M2'.
itab-week     = '3'.
itab-qty      = '22'. APPEND itab. CLEAR itab.


sort itab by material week ASCENDING.
itab1[] = itab[].



loop at itab ASSIGNING <wa>.

READ TABLE itab1 ASSIGNING <wa1> WITH  KEY  material = <wa>-material
                                             qty      = <wa>-qty.

loop at itab1  WHERE material = <wa>-material.
CHECK itab1-flag IS INITIAL.
lv_qty  = lv_qty  +  itab1-qty.
lv_week = lv_week +  1.

endloop.

if <wa1> IS NOT INITIAL.
<wa1>-flag = 'X'.
endif.

<wa>-cal = lv_qty / lv_week.
CLEAR: lv_qty, lv_week.
endloop.


loop at itab.

WRITE:/(02) itab-material,
        (04) itab-week,
        (08) itab-qty,
        (12) itab-cal.

ENDLOOP.

Try to avoid nested loop use parallel cursor in case of performance issue.

Regards,

Venkat.

[SuhaSaha]

DATA: BEGIN OF itab OCCURS 0,
       material(02) TYPE c,
       week TYPE i,
       qty  TYPE i,
       cal  TYPE i,
       flag(01) TYPE c,
       END OF itab,

Do not use internal tables with header lines.

Try to avoid nested loop use parallel cursor in case of performance issue.

Have you heard about SORTED/HASHED tables?

I have lost count of how many times i have advised you to cross-check your responses before posting. And this is not fun anymore.

- Suhas

[VenkatRamesh_V]

Dear  Suhas,

First of all thank you for replying me,

I heard about Sorted/ Hashed tables without using  header line in  internal table.

but I given some sample code with a  user specified data to get the desired output with Some logic,

User insisted on Idea ?. so I done some sample code In case of time consumption.

Because of your advice i tested the output, but i missed  Coding efficiency, Apologize me it will never happen again.

Regards,

Venkat.

[davis_raja]

I doubt that your code will work flawlessly.

READ TABLE will fail if 2 or more weeks (records) have same material number and Quantity.

[SuhaSaha]

User insisted on Idea ?. so I done some sample code In case of time consumption.

This is no excuse for posting wrong content.

It is not about posting response ASAP. If you don't have time to write a code-snippet then do not write one.

[Former Member]

Hi,

try something like that:

LOOP AT itab        INTO wa.

  CLEAR                  h_sum.

  CLEAR                  h_count.

  LOOP AT itab      INTO wa2

  WHERE material      EQ wa-material

  AND   week          GE wa-week.

    ADD 1             TO h_count.

    ADD wa2-quantity  TO h_sum.

  ENDLOOP.

  wa-calculated        = h_sum / h_count.

  MODIFY itab       FROM wa.

ENDLOOP.

Regards,

Klaus