Overall sum

[#1]

Hi,

I have the following fields:

Product, Sales Document (contains multiple product orders), Net Value

I would like to create a table that calculates for every selected product the sum of the Net Value of all deals in which this product was sold.

Eg.

Sales Doc	Product	Net Value
1	A	1000
1	B	2000
1	C	500
2	D	1500
2	E	100
2	A	4000

Result should be:

Product	Sum of Deals
A	9100 (A appears in both Sales Docs)
B	3500
C	3500
D	5600
E	5600

Hope someone can help me with the expression.

Thank you in advance!

Best regards

[tresesco]

Yes, the requirement is a bit hard for a newbie and the front-end solution is harder. You might think of solving it in the script. And don't worry, we all have/had to be a newbie for anything new.

[#3]

Thanks for your understanding how would you solve that problem in the script?

[tresesco]

Refer jagan mohan 's code above.

[#5]

How do I apply that on thousands of Sales Documents and products?

[tresesco · Accepted solution]

Replace the INLINE load by your load statement like:

Data:

LOAD

          SalesDoc,

          Product,

          NetValue

From <Yoursource>;

INLINE [

SalesDoc, Product, NetValue

1, A, 1000

1, B, 2000

1, C, 500

2, D, 1500

2, E, 100

2, A, 4000];

GroupSum:

LOAD

SalesDoc,

Sum(NetValue) AS OverallSum

RESIDENT Data

Group By SalesDoc;

[#7]

Awesome, thank you very much!

[tresesco]

I have found an easier front-end solution.

=Sum(Aggr( NODISTINCT Sum([Net Value]), [Sales Doc]))

[senpradip007]

Thats awesome.