Skip to Content
avatar image
Former Member

Sumct function for formula issue

Jan 12, 2017 at 06:28 AM | 148 Views

Hi All,

I am facing an issue with sumct. Sumct is not getting applied on formula.

A b c c-b D
if c>B than
c-b else 0 E
if c>B than
c-b else 0 Sumct E Expected
Sumct x 100 150 50 50 50 0 150 x 200 300 100 100 100 0 150 x 60 40 -20 0 0 0 150 x 300 10 -290 0 0 0 150 X Result 660 500 -160 0 150 0 330 y 20 50 30 30 30 0 180 y 100 10 -90 0 0 0 180 y 50 20 -30 0 0 0 180 Y Result 830 580 -250 0 180 0 330

In the above example, in column D and E formula (c-b) is applied that will calculate only if c is greater than B else 0. Subtotal is showing 0 in Column D, while in column E, i brought it through option "Calcualte Result as Summation" .

Now i am applying Sumct on column E. It is showing 0. Since in subtotal column (c is less than b)500 is less than 600, hence it shows 0.

I want sumct to show the column "Expected Sumct". Because i have to do further calculation on this sumct.

How can i achieve it.

BW Business Explorer (SAP BEx)
SAP BW/4HANA | bex query designer
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Jan 12, 2017 at 07:25 AM

    To explain it simple

    I have 2 column a and b.

    Third column C is a formula with condition (if b>a, than b – a, else 0).

    If there are two row. In first row a = 50 and b = 100, c will show as 50.

    In second row a=100 and b= 20, c will show 0 as (b<a).

    Now if I am applying sumct on C, it is displaying 0.

    Sumct is getting applied on subtotal of a and b. i.e. a= 150 and b=120, hence it is showing 0.

    But I want it to show 50, as I have to do further calculation on it.

    How can this be achieved.

    Kindly Help.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Francisco Milán Campos
    Jan 12, 2017 at 06:15 PM

    Hi Mariadas Rajamani,

    First of of all, it seems to me that using the Function Sumct might not be the best solution for the logic you want to implement, I got this conclusion after taking a look at the documentation for SUMCT, in the link below you can see an example of how this Function works.
    https://help.sap.com/saphelp_nw70/helpdata/en/03/17f13a2f160f28e10000000a114084/content.htm

    Now, I would recommend to take a look at the link below to understand how an "IF-THEN-ELSE" expression can be built in Query Analyzer.
    https://help.sap.com/saphelp_nw73/helpdata/en/23/17f13a2f160f28e10000000a114084/content.htm

    Finally, you might also need to take into account the "Formula Collision" behavior in your query.
    https://help.sap.com/saphelp_nw70/helpdata/EN/d2/02223c5f00612be10000000a11402f/content.htm

    Good luck,

    Francisco Milán.

    Add comment
    10|10000 characters needed characters exceeded