topic Re: Calculate Sums from unlinked keys in QlikView

Calculate Sums from unlinked keys

Anonymous — Thu, 05 Mar 2015 11:49:36 GMT

Hello !!

my actual Problem is, that have the following 2 tables with product turnover data and product stock data. I know, that the productnumber is equal in both tables but I do not want QlikView script to join both fields, as my Intention is to get the sum of the turnover and additional stock data without influencing one selection with the other. I hope my example witll help to get answers from you all.

Table1:

ProductId1	Turnover
Prod1	100
Prod2	120
Prod3	80
Prod4	90

Table2:

ProductId2	Stock	OnStock
Prod1	AT	5
Prod1	DE	1
Prod2	AT	3
Prod3	AT	10
Prod3	DE	1
Prod4	DE	6

Now I want to have a diagram Chart with the following totals:

When I do not select a Stock, I want to see:

ProductId1	Turnover	OnStock
Prod1	100	6
Prod2	120	3
Prod3	80	11
Prod4	90	6

When I select "AT", I want to see:

ProductId1	Turnover	OnStock
Prod1	100	5
Prod2	120	3
Prod3	80	10
Prod4	90	0

Any idea, how I can achieve this ?

Best regards, Sascha

Re: Calculate Sums from unlinked keys

simenkg — Thu, 05 Mar 2015 11:54:27 GMT

You can join the tables or keep them separate but as long as the Field name is identical in them both they will be linked. But this is what you want.

When they are linked your expressions become:

Turnover:
Sum({<Stock=>} Turnover)

OnStock:
Sum(OnStock)

Re: Calculate Sums from unlinked keys

sunny_talwar — Thu, 05 Mar 2015 12:08:54 GMT

PFA

Best,

Re: Calculate Sums from unlinked keys

Anonymous — Thu, 05 Mar 2015 12:16:40 GMT

Hi, your example is great, but des this mean, I have to add the {<Stock=>} restriction to all my summaries in my existing QlikView App (because my example has been only a part of the whole) ? I hoped, I could have a solution I only have to add to the sum of teh OnStock value without the Need to Change all the other formulas.

Re: Calculate Sums from unlinked keys

sunny_talwar — Thu, 05 Mar 2015 12:43:26 GMT

If you don't want a particular expression not to change on a selection for a particular field name, you have to specify that within your expression. Else the expression won't know when to change and when not to change.

HTH

Best,

Re: Calculate Sums from unlinked keys

Anonymous — Thu, 05 Mar 2015 13:00:49 GMT

OK, I will have to take that into account. I hoped there is an expression like "sum(<{ProductId2 = ProductId1}>OnStock)" or something similar to get it done this way.

Anyway, thanks for your quick Feedback.

Re: Calculate Sums from unlinked keys

sunny_talwar — Thu, 05 Mar 2015 13:02:53 GMT

Not a problem. Have a good day.

Best,