topic Strange SUM behavior in QlikView

Strange SUM behavior

Fri, 18 Jul 2014 06:22:14 GMT

Hi,

I've got a strange issue where 'sum' function is acting as if there was a distinct command.

Here I've got my individual line items:

And here I've got the items grouped under it's product code.

You'll notice though that while sum ForecastedDetailActualAmt is correct, sum Disti Cost is wrong and seems to have picked the two distinct/different numbers to add together (31525 + 38768). As you can see I don't have distinct in my expression.

Any idea what's going on? This is the first I've seen this happen.

Thanks.

Carlo

Re: Strange SUM behavior

hic — Fri, 18 Jul 2014 06:29:08 GMT

I think that this is what you have in your data model and that QlikView shows the correct numbers. But to be sure, I need to look at the data model.

See more on Totals in Charts.

HIC

Re: Strange SUM behavior

SunilChauhan — Fri, 18 Jul 2014 06:34:03 GMT

go to chart properties-> select your expression(Sum([Disti Cost])-> Total Mode-> Sum in drop down

hope this helps

Re: Strange SUM behavior

jagan — Fri, 18 Jul 2014 06:53:02 GMT

Hi,

Can you attach some sample file, so that it would be easier to check.

Regards,

Jagan.

Re: Strange SUM behavior

Fri, 18 Jul 2014 06:56:06 GMT

Hi Henric,

Appreciate the help - thanks. Do you want the qvw file?

Regards,

Carlo

Re: Strange SUM behavior

hic — Fri, 18 Jul 2014 07:16:40 GMT

It would help if you post it. I am not sure that I will have time to look at it, but there are plenty of other smart people on this community.

I suspect that you only have the two records in the data, and that one of them appears on two lines in your chart due to one of the dimensions. It is pretty much along the lines of the "many-to-many relationship" described in the blog post. Then the sum is correct the way it is.

HIC

Re: Strange SUM behavior

Fri, 18 Jul 2014 07:21:36 GMT

Could you please send the QVW file

Re: Strange SUM behavior

Fri, 18 Jul 2014 07:31:11 GMT

Hi All,

Here's the qvw file.

Cheers,

Carlo

Re: Strange SUM behavior

Fri, 18 Jul 2014 07:39:44 GMT

I think you missed the attachment

Re: Strange SUM behavior

hic — Fri, 18 Jul 2014 07:44:47 GMT

You need to re-think your data model.

HIC

Re: Strange SUM behavior

Fri, 18 Jul 2014 08:20:43 GMT

Hi,

I think the correct sum is the second, the first one seems duplicated...The table "pricelist" has less granularity than "com_v_apac...".

Apart from that the behaviour is strange maybe because of all that syntetic keys you have. The syntetic keys affects the behaviour of the data and the performance of the model.

You should aviod them joining tables or creating keys composed by several concatenated fields.

Hope that helps

Cheers,

David

Re: Strange SUM behavior

Mon, 21 Jul 2014 01:38:30 GMT

Hi All,

Thanks for all the help - really appreciate it! I've done further testing based on your feedback. I've greatly simplified the data model to have only two sources as follows:

However, I'm still having problems summing up my disti cost. It appears that if the disti cost is not unique - it doesn't get counted. For instance, if i pick a product (SKU) under Australia - then the sum function only comes up with a single row value instead of summing up all of the rows that come under those parameters.

Having read Henric's suggestions - is the problem caused by the fact that the same product (SKU) appear under multiple countries/regions? How do i overcome this? Will using aggr function assist?

Thanks,

Carlo

Re: Strange SUM behavior

jagan — Mon, 21 Jul 2014 02:07:46 GMT

Hi,

Arrive a composite key for the two columns in both the tables and rename/drop the region and SKU columns in fact table. Please check the script below

FactTable:

LOAD

Region & '_' & SKU AS RegionSKUKey,

FROM FactTable;

PriceList:

LOAD

Region & '_' & SKU AS RegionSKUKey,

FROM FactTable;

DROP Fields Region, SKU FROM FactTable;

Hope this helps you.

Regards,

Jagan.

Re: Re: Strange SUM behavior

felipe_dutra — Mon, 21 Jul 2014 02:50:02 GMT

Look this:

Re: Strange SUM behavior

preminqlik — Mon, 21 Jul 2014 04:25:39 GMT

use this

sum(aggr(sum([Dist Cost]),SKU,Country,Opty))

Re: Strange SUM behavior

jonathandienst — Mon, 21 Jul 2014 05:44:47 GMT

Simplifying your model will improve performance, but will not affect your issue. You are summing disti cost by region - this does not involve the other table(s) at all, only the pricelist table. So it is summing disti cost by region without considering the quantities in the com_v_apac.... table. If disti cost is a unit price, then it is meaningless summing this any way.

If you need to consider the quantity in com_v_apac... you will need to bring this into the expression in some way - for example a unit cost from pricelist and a quantity from com_v_apac...

HTH

Jonathan