topic Problems with "Sum" in QlikView

Problems with "Sum"

hobanwashburne — Thu, 24 Jul 2014 12:39:49 GMT

I have a fairly large data set of invoice history linked on invoice date to a master calendar. When I create either a chart or pivot table from the data set where the Dimensions are month (from the master calendar table) and the Expression is sum(NetPrice_Extended) I get an incorrect result (exactly 2 times what it should be). However, when I export the same data set to Excel (via table box) and create a pivot table in Excel I get the correct result.

Example:

Result from Qlikview= 2,348,336

Result from Excel (verified correct)= 1,174,167

Result from Qlikview = 1,246,473

Result from Excel (verified correct) = 623,236

Re: Problems with "Sum"

datanibbler — Thu, 24 Jul 2014 12:45:37 GMT

Hi,

a tablebox only shows you all the possible combinations of values that you have. If you have duplicates in your table - sounds a bit like you might have every record twice - it wouldn't show you that unless you use a RowNo() as a unique ID_field in every record. That's why, when doing a tablebox and exporting to Excel, you get the correct result.

Re: Problems with "Sum"

MK_QSL — Thu, 24 Jul 2014 12:52:13 GMT

Add

RowNo() as No

while loading the table in your script....

This should work...

Re: Problems with "Sum"

hobanwashburne — Thu, 24 Jul 2014 13:10:14 GMT

Adding RowNo() in the load did not fix the problem, but it did confirm DataNibbler's suspicion. Any ideas on how I could have ended up with exactly double the records in my QVD creation - or how during my QVD creation I can remove duplicates?

Re: Problems with "Sum"

Thu, 24 Jul 2014 15:37:18 GMT

Duplicates usually occur as a result of joins. Was any other table joined to the original table which has the required fields.

Re: Problems with "Sum"

hobanwashburne — Thu, 24 Jul 2014 15:44:29 GMT

Yes, there are multiple joins in the QVD creation. I believe that I have solved the issue by adding "Distinct" to the load of the primary fact table.