Strange SUM behavior

[#1]

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

[#2]

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

[#3]

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

[jagan]

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.

[felipe_dutra]

Look this:

[preminqlik]

use this

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

[jonathandienst]

Hi

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