topic Re: Overall sum in QlikView

Overall sum

Wed, 08 Oct 2014 08:43:08 GMT

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

Re: Overall sum

vinay_hg — Wed, 08 Oct 2014 09:07:55 GMT

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.

Re: Overall sum

Wed, 08 Oct 2014 09:10:15 GMT

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

Just replicate for B,C,D,& E

Re: Overall sum

tresB — Wed, 08 Oct 2014 09:24:22 GMT

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

I am afraid to explain it in details.

Re: Overall sum

jagan — Wed, 08 Oct 2014 09:53:19 GMT

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.

Re: Overall sum

Wed, 08 Oct 2014 10:35:08 GMT

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

Re: Overall sum

Wed, 08 Oct 2014 11:23:00 GMT

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

Re: Re: Overall sum

Wed, 08 Oct 2014 11:25:58 GMT

Dear Ludwig,

Please find the attached QVW.

Thanks & Regards

Prince Anand

Re: Overall sum

tresB — Wed, 08 Oct 2014 11:32:15 GMT

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)?

Re: Overall sum

Wed, 08 Oct 2014 11:45:09 GMT

To be honest, I have no idea

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

Re: Overall sum

tresB — Wed, 08 Oct 2014 11:56:09 GMT

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.

Re: Overall sum

Wed, 08 Oct 2014 12:01:06 GMT

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

Re: Overall sum

tresB — Wed, 08 Oct 2014 12:05:22 GMT

Refer jagan mohan 's code above.

Re: Overall sum

Wed, 08 Oct 2014 12:10:03 GMT

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

Re: Overall sum

tresB — Wed, 08 Oct 2014 12:15:42 GMT

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;

Re: Overall sum

Wed, 08 Oct 2014 12:26:57 GMT

Awesome, thank you very much!

Re: Re: Overall sum

tresB — Wed, 08 Oct 2014 12:44:26 GMT

I have found an easier front-end solution.

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

Re: Overall sum

senpradip007 — Wed, 08 Oct 2014 12:58:36 GMT

Thats awesome.