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

[#2]

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

[#3]

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

[brenner_martina]

Hi, Ian,

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

[#5]

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

[#6]

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

[#7]

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!

[brenner_martina]

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

[ToniKautto]

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

[#10]

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