topic Re: Simple division question in Application Development and Automation Discussions

Simple division question

former_member559196 — Thu, 04 Jun 2020 14:24:34 GMT

I'm looking to divide the sum of line items for percentage. I have used at last & sum in a loop which provides the sum for each but having difficulty coding the division into a result. I've specified DATA: result TYPE p DECIMALS 2.

Re: Simple division question

MateuszAdamus — Thu, 04 Jun 2020 14:42:52 GMT

Hi Brian,

Please provide your code. It will be much easier to replicate your issue.

You're trying to calculate the percentage of what?

Kind regards,
Mateusz

Re: Simple division question

Sandra_Rossi — Thu, 04 Jun 2020 14:50:59 GMT

SUM is officially obsolete. AT LAST is almost obsolete.

Re: Simple division question

former_member559196 — Thu, 04 Jun 2020 14:56:54 GMT

the ordered and delivered amount from me23n.

TABLES: essr, ekpo.

TYPES: BEGIN OF ty_essr,
ebeln TYPE essr-ebeln,
ebelp TYPE essr-ebelp,
netwr TYPE essr-netwr,
END OF ty_essr.

DATA: wa_essr TYPE ty_essr,
it_essr TYPE TABLE OF ty_essr WITH HEADER LINE.

TYPES: BEGIN OF ty_ekpo,
netpr TYPE ekpo-netpr,
END OF ty_ekpo.

DATA: wa_ekpo TYPE ty_ekpo,
it_ekpo TYPE TABLE OF ty_ekpo WITH HEADER LINE.

DATA: result TYPE p DECIMALS 2.

INITIALIZATION.
SELECT-OPTIONS: s_ebeln FOR essr-ebeln.

START-OF-SELECTION.
SELECT ebeln ebelp netwr
FROM essr INTO TABLE it_essr
WHERE ebeln IN s_ebeln.

SELECT netpr
FROM ekpo INTO TABLE it_ekpo
WHERE ebeln IN s_ebeln.

SORT it_essr.

LOOP AT it_essr INTO wa_essr.
AT LAST.
SUM.
WRITE:
/ 'ORDERED: ' COLOR 4,
27 wa_essr-netwr.
ENDAT.
CLEAR: wa_essr.
ENDLOOP.

LOOP AT it_ekpo INTO wa_ekpo.
AT LAST.
SUM.
WRITE:
/ 'DELIVERED: ' COLOR 4,
27 wa_ekpo-netpr.
ENDAT.
CLEAR: wa_ekpo.
ENDLOOP.

WRITE: / 'Percent Complete: ' COLOR 5,
19 result.

Re: Simple division question

MateuszAdamus — Thu, 04 Jun 2020 15:00:30 GMT

Hello Brian,

Answer option is for answer with the solution to the question. Better to use the "Comment" option.

Also, it would be nice if you put your code in the "Code" tag of the editor - for better readability.

Kind regards,
Mateusz

Re: Simple division question

MateuszAdamus — Thu, 04 Jun 2020 15:05:17 GMT

Hello bcooper

To answer your issue - what you're doing is calculating and printing the sums, but you're not storing these anywhere. To be able to calculate the percentage you need to store the sums of each group and then calculate the percentage.

As far as I can see you're not using any conditions for the LOOP, so you're basically summing up all records. Correct?

If that's the case, then I'd propose you change your code as follows.

DATA:
  lv_essr_sum LIKE essr-netwr,
  lv_ekpo_sum LIKE ekpo-netwr.

SORT it_essr BY ebeln ebelp.
SORT it_ekpo BY ebeln.

LOOP AT it_essr INTO wa_essr.
  AT NEW ebeln.
    CLEAR:
      lv_essr_sum,
      lv_ekpo_sum.
  ENDAT.

  lv_essr_sum = lv_essr_sum + wa_essr-netwr.

  LOOP AT it_ekpo INTO wa_ekpo WHERE ebeln = wa_essr-ebeln.
    lv_ekpo_sum = lv_ekpo_sum + wa_ekpo-netpr.
  ENDLOOP.

  AT END OF ebeln.
    WRITE: / 'ORDERED: ' COLOR 4, 27 lv_essr_sum.
    WRITE: / 'DELIVERED: ' COLOR 4, 27 lv_ekpo_sum.

    IF lv_essr_sum > 0.
      result = ( lv_ekpo_sum * 100 ) / lv_essr_sum.
    ELSE.
      result = 0.
    ENDIF.

    WRITE: / 'Percent Complete: ' COLOR 5, 19 result.
  ENDAT.
ENDLOOP.

Kind regards,
Mateusz

Re: Simple division question

MateuszAdamus — Thu, 04 Jun 2020 15:19:29 GMT

Actually, if this is the only thing that your report does, probably not, but if, then you could sum the amounts in SQL and then skip the summing in LOOPs.

  SELECT ebeln, SUM( netwr ) AS netwr
    FROM essr
    INTO TABLE @DATA(it_essr)
    WHERE ebeln IN @s_ebeln
    GROUP BY ebeln.

  SELECT ebeln, SUM( netpr ) AS netpr
    FROM ekpo
    INTO TABLE @DATA(it_ekpo)
    WHERE ebeln IN @s_ebeln
    GROUP BY ebeln.

Kind regards,
Mateusz

Re: Simple division question

former_member559196 — Thu, 04 Jun 2020 20:29:29 GMT

Hi Mateusz

Thanks for the forum tips, I will use accordingly in future posts. Also, you are correct, I'm looking to calculate and store the sum as this I was unable to do and yes, I'm taking the sum of all line items. The code is part of a report but I do like sum amounts in SQL vs loops, very helpful. Thanks for your time and assistance.

Re: Simple division question

Sandra_Rossi — Fri, 05 Jun 2020 05:23:27 GMT

"LOOP AT it_essr ... AT NEW ebeln" will work well in all cases only if ebeln is at the first position of the line type, that's the case as per the definition given per Brian:

TYPES: BEGIN OF ty_essr,
      ebeln TYPE essr-ebeln,
      ebelp TYPE essr-ebelp,
      netwr TYPE essr-netwr,
    END OF ty_essr.

Re: Simple division question

MateuszAdamus — Fri, 05 Jun 2020 06:28:58 GMT

Hello sandra.rossi

That's not necessary true. If there were any WHERE conditions for the LOOP, there might be cases were the records are not selected in proper order thus making the AT NEW block not fire. Consider this simple example.

SELECT *
  FROM vbap
  INTO TABLE @DATA(lt_vbap)
  UP TO 100 ROWS.

SORT lt_vbap BY vbeln posnr.
LOOP AT lt_vbap REFERENCE INTO DATA(ld_vbap)
  WHERE posnr = 20.

  AT NEW vbeln.
    WRITE: / ld_vbap->vbeln.
  ENDAT.
ENDLOOP.

In this case the AT NEW vbeln block will never be executed, even though we can clearly see, that the VBELN value will change in sequence.

Reason for this is, I assume, that block firing places (so to speak) are defined on the internal table level, not on the LOOP level. From the documentation:

Apart from the special variants AT FIRST and AT LAST, group levels are defined using the variant AT NEW and the sequence of rows in the internal table.

The following rules must be followed for effective group level processing:

A table key specified in LOOP in cond must be chosen so that it produces the required processing order of the imported rows.
A restricting condition specified in LOOP in cond must select a contiguous row block in the internal table.

Kind regards,

Mateusz

Re: Simple division question

Sandra_Rossi — Fri, 05 Jun 2020 07:14:49 GMT

Sorry, I think I was not clear. I meant that if your type is defined like below (ebelp before ebeln, or any other type before ebeln), AT NEW vbeln won't work well at all because it would mean "at any change of ebelp or ebeln". That's just a well known error-prone aspect of AT NEW.

TYPES: BEGIN OF ty_essr,
      ebelp TYPE essr-ebelp, " not good to have something before ebeln
      ebeln TYPE essr-ebeln,
      netwr TYPE essr-netwr,
    END OF ty_essr.

PS: I'm not talking about the SORT.

Re: Simple division question

MateuszAdamus — Fri, 05 Jun 2020 07:35:00 GMT

Yeap, that's right.