Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi, Ian,

at first, you have THREE Syntetic tables!!!! That's wrong, QV does not like them! Combine fields in two tables to create a new key, so that the syntetic tables disapear!

second: the product_Details VOLUME is always the same?

Not applicable
Author

Hi Martina,

Okay, this is great feedback - please remember this is my first application I am building and I have had no training . . . so I am as newbie as anyone gets. 😞

How do I combine fields to create a new key? So for instance, $Syn 1 Table is a combination of SITE_CODE and LOC_CODE as this is what guarantees uniqueness. Can you briefly explain how to get rid of this $Syn 1 Tab? There were 7 synthetic tables when the consultant left, so I was pretty chuffed to tidy everything up and get it to 3 🙂

Secondly, what seems to change is if you select the properties for <Location Volume by Site and Status> pivot table and then select the second expression <Product Volume>. There should be two versions of the calculation, one using sum and one without using sum. The calculation should be the sum of all quantities of the possibly various products multiplied by their individual product volume to give a total volume. I get this with no sum, but then no subtotals and the pie chart stops working. If I use the version that sums everything, I get the incorrect field values inflated by 2x or 4x the expected value. But the pie chart works - even if it is with the wrong total?

The same thing is happening when the individual quantities are summed - the total is wrong? Should be 98 if you look at the pivot table, but the pie chart gives it as 128? Which looks like the value of 30 may have been counted twice.

As I said this is my first applicaqtion and the last time I was hands on with SQL and DBs was at university over 25 years back 😉 So gentleness for the old dog is requested.

Thanks
Ian

Not applicable
Author

I changed the formula to include the SUM and then exported the pivot table to Excel. In the highlighted yellow portion on the right, I have manually calculated the expected volumes and the difference is hightlighted. Worksheet is attached.

Cheers
Ian

brenner_martina
Partner - Specialist II
Partner - Specialist II

Hi,

your script could look like this:

Location_Details:
LOAD
// Load Table Keys
SITE_CODE & '/' & LOC_CODE As KeySideLocCode,
SITE_CODE As [Key Site_Details SITE_CODE],
LOC_CODE As [Key Location_Details LOC_CODE],
SITE_CODE As [Key Stock_Ledger_Detail SITE_CODE],
LOC_CODE As [Key Stock_Ledger_Detail LOC_CODE],
// Load Table Data
SITE_CODE As [Location_Details SITE_CODE],
LOC_CODE As [Location_Details LOC_CODE],
// A constant is imported in [Site_Details Data Source] to help troubleshoot if the same location is imported from different WMS's
'GACware (Unite)' As [Location_Details Data Source],
LOC_DESC As [Location_Details LOC_DESC],
If(LOC_STAT ='M','Mixed',If(LOC_STAT='E','Empty',If(LOC_STAT ='F','Full',If(LOC_STAT ='R','Reserved',If(LOC_STAT ='P','Pickslot',If(LOC_STAT ='B','Bad',If(LOC_STAT ='','Bulk'))))))) As [Location_Details LOC_STAT],
AISLE As [Location_Details AISLE],
COLUMN As [Location_Details COLUMN],
HEIGHT As [Location_Details HEIGHT];
SQL SELECT *
// FROM TESTDB.dbo.LOCATION WHERE LOC_STAT<>'B';
FROM GACWAREDB.dbo.LOCATION WHERE LOC_STAT<>'B';

DISCONNECT;

// ODBC CONNECT TO GACWARE_DRP (XUserId is TALLXUFMObaON, XPassword is IRcKDYFMTbcOXXJOETQA);
ODBC CONNECT TO GACWARE_LIVE (XUserId is RPRPXUFMObaAN, XPassword is aIfUNYFMTbcOXXJOEDZB);

Concatenate
//Location_Details:
LOAD
// Load Table Keys
SITE_CODE & '/' & LOC_CODE As KeySideLocCode,
SITE_CODE As [Key Site_Details SITE_CODE],
LOC_CODE As [Key Location_Details LOC_CODE],
SITE_CODE As [Key Stock_Ledger_Detail SITE_CODE],
LOC_CODE As [Key Stock_Ledger_Detail LOC_CODE],
// Load Table Data
SITE_CODE As [Location_Details SITE_CODE],
LOC_CODE As [Location_Details LOC_CODE],
// A constant is imported in [Site_Details Data Source] to help troubleshoot if the same location is imported from different WMS's
'GACware (Dubai)' As [Location_Details Data Source],
LOC_DESC As [Location_Details LOC_DESC],
If(LOC_STAT ='M','Mixed',If(LOC_STAT='E','Empty',If(LOC_STAT ='F','Full',If(LOC_STAT ='R','Reserved',If(LOC_STAT ='P','Pickslot',If(LOC_STAT ='B','Bad',If(LOC_STAT ='','Bulk'))))))) As [Location_Details LOC_STAT],
AISLE As [Location_Details AISLE],
COLUMN As [Location_Details COLUMN],
HEIGHT As [Location_Details HEIGHT];
SQL SELECT *
FROM GACWAREDB.dbo.LOCATION WHERE LOC_STAT<>'B';

DISCONNECT;


// ODBC CONNECT TO TEST_GACWARE (XUserId is NYfWZUFMObaCDYAGQZCB, XPassword is KOLMIYFMTbcOXXJOEDcB);
ODBC CONNECT TO GACWAREunite (XUserId is LFRLWUFMObaCDYAGQBOA, XPassword is aUdRdDNKVTcMWYYNHLMGHGFGGLZOXYEd);

Stock_Ledger_Detail:
LOAD
// Load Table Keys
SITE_CODE & '/' & LOC_CODE As KeySideLocCode,
// SITE_CODE As [Key Stock_Ledger_Detail SITE_CODE],
// LOC_CODE As [Key Stock_Ledger_Detail LOC_CODE],
PRIN_CODE As [Key Principal_Master PRIN_CODE],
PROD_CODE As [Key Product_Details PROD_CODE],
// Load Table Data
SITE_CODE As [Stock_Ledger_Detail SITE_CODE],
LOC_CODE As [Stock_Ledger_Detail LOC_CODE],
PRIN_CODE As [Stock_Ledger_Detail PRIN_CODE],
PROD_CODE As [Stock_Ledger_Detail PROD_CODE],
QTYS As [Stock_Ledger_Detail QTYS];
SQL SELECT *
// FROM TESTDB.dbo.STKLED;
FROM GACWAREDB.dbo.STKLED;

DISCONNECT;

// ODBC CONNECT TO GACWARE_DRP (XUserId is TALLXUFMObaON, XPassword is IRcKDYFMTbcOXXJOETQA);
ODBC CONNECT TO GACWARE_LIVE (XUserId is RPRPXUFMObaAN, XPassword is aIfUNYFMTbcOXXJOEDZB);

Concatenate
//Stock_Ledger_Detail:
LOAD
// Load Table Keys
SITE_CODE & '/' & LOC_CODE As KeySideLocCode,
// SITE_CODE As [Key Stock_Ledger_Detail SITE_CODE],
// LOC_CODE As [Key Stock_Ledger_Detail LOC_CODE],
PRIN_CODE As [Key Principal_Master PRIN_CODE],
PROD_CODE As [Key Product_Details PROD_CODE],
// Load Table Data
SITE_CODE As [Stock_Ledger_Detail SITE_CODE],
LOC_CODE As [Stock_Ledger_Detail LOC_CODE],
PRIN_CODE As [Stock_Ledger_Detail PRIN_CODE],
PROD_CODE As [Stock_Ledger_Detail PROD_CODE],
QTYS As [Stock_Ledger_Detail QTYS];
SQL SELECT *
FROM GACWAREDB.dbo.STKLED;

DISCONNECT;

ToniKautto
Employee
Employee

The aim for the data model should be to have one key per table, to get simple links between tables.

Just as Martina exemplified, an easy way to resolve the synthetic key is to create a new key-field where the multiple keys are concatentated into one single value.

Looking at your model I would also recommend that you try to organise the tables to a more star like schema, instead of the chain you have now. Meaning that you have one central fact table to which you connect attribute tables. This will make Qlikview calculations quicker, and also easier for you as developer/Designer to interpret the data relations .

It is also highly encouraged that you take the time to look at QlikView's online training material as this will cover a lot of fundamental topics to become a QlikView pro. Working with scripts the Developer training is something that will benefit you the most.

http://qlik.com/training



Not applicable
Author

Thanks Martina and Toni,

Okay, I can see how combining the keys will get rid of the synthetic key, but how do I link back to say the Principal table so I have access to the more user friendly Principal Name as opposed to just the Principle Code?

Secondly, the star data model sounds far more intuitive - I have struggled for several days just to get something like that linked correctly (or so I thought). Do you have any suggestions for the changes required to turn my chain into your star? 🙂

Thirdly, regardless of the changes, can anyone see the differences that seem to show up in using SUM and not using it? Or do you think this is a consequence of the synthetic keys?

You cannot believe how helpful you people have been - I have done some of the online training . . . clearly a little classroom training is needed, but it is not offered in the UAE.

Regards
Ian

Not applicable
Author

Okay,

Further to my last mail, I have done as suggested by Martina and appear to have got rid of the synthetic keys . . . not sure I have completely got my head around it just yet . . . but closer.

I saved a slightly larger dataset this time - 3 locations - I changed the expression to include the sum of all volumes used in the location. I then exported the pivot table to Excel - added tocolumns to show what the volume x quantity should be and highlight the difference.

I feel like I have progressed . . . even if it is still not yielding the correct answer. So . . . next steps to narrow down the problem?

Thanks peeps. Much appreciated.

Regards
Ian

Not applicable
Author

And the Excel sheet as I seem to only be able to add one file and not multiple ones.

Thanks
Ian

ToniKautto
Employee
Employee

I am quite convinced that the root cause of your problem is in the data structure. In this context you must keep in mind that QV will base it calculation work on a data set corresponding to the current selections. This data set is then the current combinations of data based on selection and the fields used in the object, so if you have one field in table A and an other in table D the relation is spread over table A-B-C-D. There is no problem to have this type of data relation in theory.

The problem begin in the scenario where your data does not have a logical connection over all tables. In these cases QV will start finding a way around and there by creating all possible combination of rows in the data set. Typical symptom is that your SUM operations result on larger numbers, as their are more rows in the data set. By using a Star schema you are unlikley to end up in this situation as your tables are closely related to each other. Then of course you can have several stars in your schema and tie them so together into a snow flake schema.

Unfortunately I do not have the possibility to dig deeper into your application, this is usually something directed to QlikView consultants in order to get help on the way forward with app development.

Find more details about consulting at qlik.com > Services > Consulting

Not applicable
Author

Hi Ian

I seem to be having the same issue when it comes to averaging some data ,

Totally think its my data structured, did you get resolve for your query ?