topic Re: average calculation for different rows in one internal table in Application Development and Automation Discussions

average calculation for different rows in one internal table

Former Member — Fri, 26 Oct 2018 11:20:23 GMT

Hello Experts,
I have a requirement to calculate the average of sales values for different materials
to do this I have gathered all the data in an internal Table which has flowing columns and rows:

Material number, sales value ,Priods

10000, 500 ,2018.6

10000 ,600 ,2018.7

10000 ,400, 2018.8

10001 ,10000, 2018.8

10001, 9500 ,2018.7 10002 532 2018.8

* Now I am trying to calculate the average sales vlaues for the last two periods for each material number.

To do this I have coded as below :

LOOP AT lt_show_avg INTO ls_show_avg.
  COLLECT  ls_show_avg INTO lt_show_avg_final .
ENDLOOP.
**********************************************************************

SORT lt_show_avg_final BY  MATNR priod DESCENDING.
**********************************************************************
* here the problem will start
LOOP AT lt_show_avg_final INTO ls_show_avg_final . 
  ADD 1 TO lv_count.
  IF lv_count > 2.
    DELETE lt_show_avg_final.

  ENDIF.
ENDLOOP.
*--------------------------------------------------------------------*

LOOP AT lt_show_avg_final INTO ls_show_avg_final.
  CLEAR ls_show_avg_final.
  ADD 1 TO lv_count_final.
  AT LAST.
    SUM.
    lv_total_expns = ls_show_avg_final-expns.
    lv_avg_expns = lv_total_expns  / lv_count_final.
  ENDAT.
APPEND ls_show_avg_final to lt_show_avg_final
ENDLOOP.

everything is ok when I am trying with one material number, but when I am entering many numbers then the problem will appear, cause I will read just the first two rows and all the calculation process will go further wrongly.

What I want to show is as the follwing

* Priods are not necessary to be shown anymore

Material number, sales value, Priods, Sales Average

10000 ,1100 ,2018.8 +2018.7 ,550

10001 ,19500, 2018.8+2018.7 ,9750

10002 ,532 ,2018.8 ,532

Could you please help to achieve this?

Best Regards

Jenie

Re: average calculation for different rows in one internal table

SimoneMilesi — Fri, 26 Oct 2018 16:15:53 GMT

It's more a matter of pure logic than ABAP 🙂

You already ordered your table and this is good: now ask yourself, why it works with one material and not with multiple ones?
The reason is the statement you are using "AT LAST", which works on the VERY LAST RECORD of your internal table, regardless any other sortering nor condition.

To fix your problem, you have to use AT NEW ... and AT END OF ..... and, keeping your table ordered, you can "break" when you want (i.e. obtaining subtotals for material AND period).

Try to use them and let us know.

Re: average calculation for different rows in one internal table

pjl — Sat, 27 Oct 2018 06:15:08 GMT

Hi Jenie,

I think using the break is a good idea. Maybe this is a way how things could have been done?

    DATA count TYPE i.
    SORT lt_tab BY matnr period DESCENDING.
    LOOP AT lt_tab INTO DATA(ls_row). 
      AT NEW matnr.
        CLEAR count.
      ENDAT.
      ADD 1 TO count.
      CASE count.
        WHEN 1.
          APPEND ls_row TO r_result REFERENCE INTO DATA(lr_result_row).
        WHEN 2.
          "lr_result_row->* = VALUE #(  BASE lr_result_row->*
          "    period = |{ lr_result_row->period } + { ls_row-period }|
          "    price = ( lr_result_row->price + ls_row-price ) / 2
          ").
          lr_result_row->period = |{ lr_result_row->period } + { ls_row-period }|.
          lr_result_row->price  = ( lr_result_row->price + ls_row-price ) / 2.
      ENDCASE.
    ENDLOOP.

Cheers,

Peter

Re: average calculation for different rows in one internal table

Former Member — Tue, 30 Oct 2018 09:14:18 GMT

Many thanks Peter I will try and come back :). But my ABAP is not supporting

VALUE#(BASE


**what do I do in this case ?

Best Regards

Re: average calculation for different rows in one internal table

pjl — Tue, 30 Oct 2018 10:11:16 GMT

I updated my code by using less lines and maybe closer to your ABAP Version. You should have statet btw, that your ABAP is below 740.

Cheers,

Peter

Re: average calculation for different rows in one internal table

Former Member — Tue, 30 Oct 2018 11:02:39 GMT

Hi Peter,

cause I don't have this functionalities tow or I don't know if more date should be declared ,I have written the code below, somehow and although there are different materials numbers the count is always = 1.

LOOP AT lt_show_avg_final INTO ls_show_avg_final 

  AT NEW acctg_spec_id.

    CLEAR count.

  ENDAT.

  ADD 1 TO count.



  CASE count.



    WHEN 1.



      APPEND ls_show_avg_final TO lt_output .

    WHEN 2.

     ls_show_avg_final-avg_expns = (  ls_show_avg_final-avg_expns + ls_show_avg_final-expns ) / 2.

      APPEND  ls_show_avg_final TO lt_output .

  ENDCASE.

in this case, I had at the end the same table

lt_show_avg_final = lt_output.

your help is really much appreciated,

Best Regards

Jenie

Re: average calculation for different rows in one internal table

pjl — Tue, 30 Oct 2018 13:25:46 GMT

Hi Jenie,

my questions in the code below...

LOOP AT lt_show_avg_final INTO ls_show_avg_final 
  AT NEW acctg_spec_id. " assuming your table is ordered by this field if not, this will not work
    CLEAR count.
  ENDAT.
  ADD 1 TO count.
  CASE count.
    WHEN 1. " first line just copied
      APPEND ls_show_avg_final TO lt_output .  " is the avg_expns = expns ? If not you should do so here
      " as well you should keep a reference or a field symbol referring to this specific line.
    WHEN 2.
     " this doesnt make any sense, since you want to refer to the first line, which is not in scopy anymore
     " if you had a field symbol you could do something like <fs>-avg_expns = <fs>-avg_expns + ls_show_avg_final-expns ) / 2.
     ls_show_avg_final-avg_expns = (  ls_show_avg_final-avg_expns + ls_show_avg_final-expns ) / 2.
      APPEND  ls_show_avg_final TO lt_output . " don't do an append here, since you dont want a second 
      " line in your output. You need to modify the first line with the new average. That's why you need
      " something like a reference, what I used or a field symbol.
  ENDCASE.

Cheers,

Peter

Re: average calculation for different rows in one internal table

Former Member — Tue, 30 Oct 2018 15:07:31 GMT

Hi Peter,

Many thanks for your feedback, please find below my Input.,

LOOPAT lt_show_avg_final INTO ls_show_avg_final 
  AT NEW MATNR." this was typing mastik, sorrx for that.

CLEAR count.

ENDAT.

ADD 1 TO count.

CASE count.

WHEN 1." first line just copied

APPEND ls_show_avg_final TO lt_output ." is the avg_expns = expns ? If not you should do so here
" as well you should keep a reference or a field symbol referring to this specific line
I agree with you but could show me how this can be done as the first line has no  Avg and REFERENCE INTO DATA(lr_result_row) is not working with my ABAP version.

WHEN2.
" this doesnt make any sense, since you want to refer to the first line, which is not in scopy anymore" if you had a field symbol you could do something like <fs>-avg_expns = <fs>-avg_expns + ls_show_avg_final-expns ) / 2.
     ls_show_avg_final-avg_expns =(  ls_show_avg_final-avg_expns + ls_show_avg_final-expns )/2.

APPEND  ls_show_avg_final TO lt_output ." don't do an append here, since you dont want a second 
" line in your output. You need to modify the first line with the new average. That's why you need
" something like a reference, what I used or a field symbol.

ENDCASE.

one more Questions what is the type or the definition of lr_result_row.

Best Regards

Jenie

Re: average calculation for different rows in one internal table

pjl — Tue, 30 Oct 2018 17:12:28 GMT

Hi Jenie,

as for the structure, I have none defined. You're the one who has some structure to fill. So tell us. Especially you need to define if the input and output table structures are same or not. And if not, you need to tell us both structure definitions. Than its easier to point out.

And for the inline declaration, this can always be solved by a typical FIELD-SYMBOLS declaration somewhere in the front of your program. This should be declared as the line type of your output table.

Cheers,

Peter

Re: average calculation for different rows in one internal table

Former Member — Tue, 30 Oct 2018 18:57:24 GMT

Hi Peter ,

Many thanks for your input, the Problem that now has appeared is when I have for some materials numbers one Priode then the avg. will be zero in this case.

Do you have any idea to solve this?

Best Regards

Jenie

Re: average calculation for different rows in one internal table

Former Member — Tue, 30 Oct 2018 19:07:09 GMT

Furhter more regarding this Point ,

is the avg_expns = expns ? If not you should do so here
" as well you should keep a reference or a field symbol referring to this specific line.

could you please tell how exactly can this be done as my output structure is very simple,

Types begin of ts_output.

types Priod type zpriod

types matnr type matnr

types sales txpe zsales

endof ts_output.

data ls_output type ts_output.

data lt_output type standerd table of ts_output.

fiels-symblos <ls_output> like line of lt_output.

Re: average calculation for different rows in one internal table

Former Member — Tue, 30 Oct 2018 21:51:29 GMT

Hi Simone,

Thank your for your Input..

Best Regards

Jenie