topic Re: Differing Answers with and without SUM in QlikView

Differing Answers with and without SUM

Thu, 28 Apr 2011 08:05:34 GMT

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

Differing Answers with and without SUM

Thu, 28 Apr 2011 08:13:30 GMT

Mmmm, more and more confusing . . . but I had a small pie chart that calculated the used volume and although by removing SUM from my equation, the calculated volume per line was corrected, the total of teh three lines still showed the incorrect 1.56CBM.

So somehow Qlikview's summing process is either being misused by me or I do not understand its application.

The correct sum for the shown location is as shown in Excel: 0.596232CBM. But in both cases when the location is selected, the chart is wrong . . .

Aaaargh, silly me - okay worked this one out - the chart is using formula with SUM to total the location and I was only changing the expression in the table. Doh! Blonde post . . . but decided to post anyway to show I am at least thinking through this problem 🙂

Appreciate any help out there.

Regards
Ian

Differing Answers with and without SUM

Thu, 28 Apr 2011 08:20:10 GMT

And now . . . when I go and remove the SUM from the formula on the pie chart, I get the message that "there is no data to display".

So clearly I need some expert guidance . . . and at the moment it is not available in Dubai . . . the support engineer is in India getting married 🙂

Thanks
Ian

AW:Re: Differing Answers with and without SUM

brenner_martina — Thu, 28 Apr 2011 08:25:28 GMT

Hi, Ian,

can you upload an example? That helps us to find the problem! Thanks

AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 08:29:04 GMT

Hello Ian,

best wishes to your support engineer. I suppose he is luckier then you (at least for the moment).

To give you the needed guidance the best would be to have a litle exam app. , perhaps with anonymized (what a word) data and a textbox with your demands.

Regards, Roland

Differing Answers with and without SUM

Thu, 28 Apr 2011 08:33:18 GMT

Okay, I also had a pie chart that calculated the number of pieces in a particular location above my pivot table. The three records clearly show amounts of 28, 30 and 40. Which in my book should equal 98 . . . but the sum formula used in the pie chart:
Sum([Stock_Ledger_Detail QTYS]) shows 128 . . . which I would suspect is 28+(30+30)+40?

Why are the formulae all working so strangely . . . is something not linked correctly? I am now truly stumped.

Thank
Ian

AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 08:38:16 GMT

Uum, not quite sure how to upload an example . . . I tried by cutting and pasting the image outputs from the Pivot table.

Let me know what I should do to upload an example . . . the dataset is large . . . it is currently 9MB with hundreds of thousands of records . . . and clearly I am in the learning phase.

The data I am struggling with is sucked out of a Sybase DB from about 4 different tables. I have other charts using about 40,000 lines from a single Excel worksheet working just fine.

I am prettu sure it is just me not knowing Qlikview well enough!

AW:Re: AW:Re: Differing Answers with and without SUM

brenner_martina — Thu, 28 Apr 2011 09:08:54 GMT

Hi, Ian,

there is an option at File to save only selected data, so you can first select some data, than save this document. If you want, there is also a menupoint, to scrambling data at document properties.

And then here in the forum look at Option, to upload the small file

SV:AW:Re: AW:Re: Differing Answers with and without SUM

ToniKautto — Thu, 28 Apr 2011 09:27:50 GMT

1. Save a new copy of your file to not make your own work corupted

2. Make suitable selections to exemplify your problem, and to minimize the data/file size

3. Reduce the data; File > Reduce Data > Keep Possible values

4. Scramble dtat aif it is sensitive; Settings > Document Properties > Scrambling

5. Save the reduced and scrambled file.

6. Attach it to this thread

SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 10:04:35 GMT

Okay, done. The interesting data is one the Location Metrics tab - currently I am not using SUM in the Location Volume by Site and Status pivot table - the calculated volume per line is now correct but the SUM is not working as can be seen by the pie chart. If the SUM expression is uncommented and the other formula is commented out, you will see the change that is confusing me.

You guys are brilliant - I look forward to learning exactly where my logic is flawed as I do not like illogical things . . . and this seems to be completely illogical.

I look forward to your response.

Thank
Ian

AW:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

brenner_martina — Thu, 28 Apr 2011 10:21:30 GMT

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?

AW:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 11:24:21 GMT

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

AW:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 11:28:00 GMT

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

AW:Re: AW:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

brenner_martina — Thu, 28 Apr 2011 11:48:06 GMT

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;

SV:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

ToniKautto — Thu, 28 Apr 2011 12:06:34 GMT

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

SV:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 12:44:01 GMT

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

SV:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 14:00:49 GMT

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

SV:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 14:02:46 GMT

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

Thanks
Ian

SV:Re: SV:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

ToniKautto — Thu, 28 Apr 2011 14:23:24 GMT

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

SV:Re: SV:AW:Re: AW:Re: Differing Answers with and without SUM

Thu, 28 Apr 2011 17:03:16 GMT

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 ?