topic Re: Sum function - even there I have a issue - pls help a dummy in QlikView

Sum function - even there I have a issue - pls help a dummy

Fri, 29 Aug 2014 05:27:46 GMT

Dear community,

I have some issues that a chart shows me a unexpected result of a sum(x)/sum(y).

Can a bloody rooky ask a to easy question?

I load two tables:

and use

Sum(Sales)/Sum(MarketPotential)

as function to get a gauge chart

Can you pls help me?

Re: Sum function - even there I have a issue - pls help a dummy

tresB — Fri, 29 Aug 2014 05:39:29 GMT

If you load these two tables as it is, it will generate a synthetic key(Region+Country) and in that case, result coming is expected. So it's about key issue.

Re: Sum function - even there I have a issue - pls help a dummy

jagan — Fri, 29 Aug 2014 05:39:34 GMT

HI,

Try like this

Data:

LOAD

Trim(Region) AS Region,

Trim(Country) AS Country,

MarketPotential

FROM Table1;

LEFT JOIN(Data)

LOAD

Trim(Region) AS Region,

Trim(Country) AS Country,

Sales

FROM Table2;

Now check your formula Sum(Sales)/Sum(MarketPotential).

If not working, then attach your sample file.

Regards,

Jagan.

Re: Sum function - even there I have a issue - pls help a dummy

Fri, 29 Aug 2014 05:58:47 GMT

Dear both, thx so much for the fast reply.

Looks good but not completely correct.

Missed the information in the previous question that 'Sales' consist out of multiple datasets

e.g.

GE (Product1) 3

GE (Product2) 7

which are added up. The products can be selected as well. (sorry, In thought I have to modify the formular only).

It delivers me the following:

Re: Sum function - even there I have a issue - pls help a dummy

jagan — Fri, 29 Aug 2014 07:00:52 GMT

Hi,

Can your explain your requirement bit clearly? What is your expected output for the selection.

Regards,

Jagan.

Re: Sum function - even there I have a issue - pls help a dummy

Fri, 29 Aug 2014 07:09:20 GMT

Hi,

for EU the expected output is Sales [20] / Potential [200] =10%

With sales : [GB ProdA = 5] + [GB ProdB = 5] + [DE ProdA = 3] + [DE ProdB = 7]

Potential: [GB=100] + [DE=100]

The sales of the product should be added up and then divided by the potential market.

Thx

Torsten

Re: Sum function - even there I have a issue - pls help a dummy

tresB — Fri, 29 Aug 2014 07:31:32 GMT

Duplicate keys will cause you the issue, to avoid that if you can disregard Product, you can get it like:

Data:
LOAD Region,
Country,
MarketPotential
FROM
[QV Example.xlsx]
(ooxml, embedded labels, table is MP);

left Join

LOAD Region,
     Country,
     //Product,
     Sum(Sales) as Sales
FROM
[QV Example.xlsx]
(ooxml, embedded labels, table is Sales) group By Region, Country;

Re: Sum function - even there I have a issue - pls help a dummy

Fri, 29 Aug 2014 07:41:11 GMT

I understand, than I have to reload the data (with other var-names) for some other analysis.

Is there any way I could do this in the chart-script

Such as

Sum(total <Region> Sales) / Sum(total <Region> MarketPotential)

while loading two separate tables again?

Re: Sum function - even there I have a issue - pls help a dummy

jagan — Fri, 29 Aug 2014 07:51:42 GMT

Hi,

Try this script

Sales:

LOAD Region & '_' & Country AS Key,

Region,

Country,

Product,

Sales

FROM

[QV Example.xlsx]

(ooxml, embedded labels, table is Sales);

Data:

Left Keep(Sales)

LOAD Region & '_' & Country AS Key,

MarketPotential

FROM

[QV Example.xlsx]

(ooxml, embedded labels, table is MP);

Regards,

Jagan.

Re: Sum function - even there I have a issue - pls help a dummy

simenkg — Fri, 29 Aug 2014 07:59:39 GMT

In your example file the following expression gives you the expected output:

sum(Sales)/sum(aggr(sum({<Product={*}>}DISTINCT MarketPotential),Region,Country))

This might have to be modified to fit your full data-set, but it might help you on the way.

Regards

SKG

Re: Sum function - even there I have a issue - pls help a dummy

tresB — Fri, 29 Aug 2014 08:09:24 GMT

Try with distinct keyword for first table like:

=Sum(Sales)/Sum(distinct MarketPotential)

for Region selection it works. However this is a solution which would not always work, hence not recommended too.

Re: Sum function - even there I have a issue - pls help a dummy

alfredlee — Mon, 01 Sep 2014 03:24:09 GMT

Can you try to Load the 1st table in full but the 2nd table without the Region?

The Region-Country relationship is already defined in the 1st table, and you do not need to load that in the 2nd again.