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

[vinay_hg]

use chart straight table, put Product in dimesion in expression sum(Net value). and how u can identify product is sold?

if that is a flag use set analysis of ifcondtion on the same expression.

[#3]

sum({<Product={'A'},[Sales Doc]={'1','2'}>}[Net Value]

Just replicate for B,C,D,& E

[tresesco]

This is a tricky one. Resolved with synthetic dimension and...  PFA

I am afraid to explain it in details.

[jagan]

Hi,

Try like this

Script:

Data:

LOAD

*

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;

Now in chart:

Dimension : Product

Expression: =Sum(OverallSum)

Hope this helps you.

Regards,

Jagan.

[#6]

Dear Ludwig,

Try this:-

In Straight Table Take Product as dimension and use following expression:-

=if(aggr(concat([Sales Doc]),Product)=12,sum(total [Net Value]),

if(aggr(concat([Sales Doc]),Product)=1,sum({$<[Sales Doc]={1}>}TOTAL [Net Value]),

if(aggr(concat([Sales Doc]),Product)=2,sum({$<[Sales Doc]={2}>}total [Net Value]))))

Thanks & Regards

Prince Anand

[#7]

Thank you all for your help, unfortunately non has worked so far.

My dataset is much bigger than the short example above, maybe that should be considered.

Hope we find a solution!

Regards

[#8]

Dear Ludwig,

Please find the attached QVW.

Thanks & Regards

Prince Anand

[tresesco]

Did you try the solution I suggested above? What was the issue(yes, I understand that it's a bit complex, but propably this is the way you have to follow if you are looking for front-end solution)?

[#10]

To be honest, I have no idea

I'm rather new to QlikView, so maybe I did something wrong while adapting your file.