Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!