Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
sum({<Product={'A'},[Sales Doc]={'1','2'}>}[Net Value]
Just replicate for B,C,D,& E
This is a tricky one. Resolved with synthetic dimension and... PFA
I am afraid to explain it in details.
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.
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
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
Dear Ludwig,
Please find the attached QVW.
Thanks & Regards
Prince Anand
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)?
To be honest, I have no idea
I'm rather new to QlikView, so maybe I did something wrong while adapting your file.