Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Differing Answers with and without SUM

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:

error loading image

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 SourceSite_Details SITE_CODELocation_Details LOC_STATLocation_Details LOC_CODEStock_Ledger_Detail PROD_CODEStock_Ledger_Detail QTYSProduct_Details VOLUMEAvailable VolumeProduct VolumeCorrect AnswerFactor Bigger
GACware (Unite)B2AMixedVF01220273988280.0060841.10000.34070.1703522
GACware (Unite)B2AMixedVF01220273988300.0060841.10000.73010.182524
GACware (Unite)B2AMixedVF01220273988400.0060841.10000.48670.243362
GACware (Unite)B2AMixedTotal1.10001.55750.5962322.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:

error loading image

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

30 Replies
Highlighted
Creator
Creator

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!