Skip to Content
0

Sumct function for formula issue

Jan 12, 2017 at 06:28 AM

108

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Mariadas Rajamani Jan 12, 2017 at 07:25 AM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Francisco Milán Campos Jan 12, 2017 at 06:15 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded