Differing Answers with and without SUM

[#1]

Hi,

Although an expert with Excel, I am relatively new to the Qlikview community - but loving the power and speed of the application. However, I am having some strange results that I do not understand . . . and they are throwing the dashboard way off course.

I am selecting several records and based on the number and the volume of a product, I can calculate the volume of product at a particular location - and there could be several products at the same location. But the results are not as I expected - and potentially point to me not understanding the use of SUM exactly.

If I use the following formula: Sum([Stock_Ledger_Detail QTYS]*[Product_Details VOLUME]) I get the incorrect answers for the product volume:

I exported the above data to Excel and manually multiplied the Stock Ledger Quantities by the Product Volume to show the right answers:

Location_Details Data Source	Site_Details SITE_CODE	Location_Details LOC_STAT	Location_Details LOC_CODE	Stock_Ledger_Detail PROD_CODE	Stock_Ledger_Detail QTYS	Product_Details VOLUME	Available Volume	Product Volume	Correct Answer	Factor Bigger
GACware (Unite)	B2A	Mixed	VF01220	273988	28	0.006084	1.1000	0.3407	0.170352	2
GACware (Unite)	B2A	Mixed	VF01220	273988	30	0.006084	1.1000	0.7301	0.18252	4
GACware (Unite)	B2A	Mixed	VF01220	273988	40	0.006084	1.1000	0.4867	0.24336	2
GACware (Unite)	B2A	Mixed	Total				1.1000	1.5575	0.596232	2.612245

Using SUM, the answers calculated by Qlikview are a factor of either 2 or 4 larger?

If I use the same as above, but drop the SUM portion, then the product volumes are correct, but I lose the sub-totals:

Can someone tell me why SUM behaves like this? And yet the Available volume calculation, also using SUM, is smart enough to know that there is a single location and that the volume is not the sum of the three records, but is 1.1CBM?

I am lost and need some enlightenment . . . please.

Thanks
Ian

[MichaelTerenzi]

Hi Ian,

I think what you are running into here is how you are handling duplicate records in a pivot table. For more information, open QlikView and hit F1, then search 'aggr'>Nested Aggregations and Related Issues.

I've modified the qvw just a little and created a Straight table which shows the duplicates and sums them correctly, and also a modified pivot table with some adjustments in the expression...I'm not sure what you are trying to do or how you want to sum across your dimensions, but I think this will set you in the right direction.

Cheers!