Problems with "Sum"

[hobanwashburne]

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

[datanibbler]

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.

[MK_QSL]

Add

RowNo() as No

while loading the table in your script....

This should work...

[hobanwashburne]

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?

[#5]

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

[hobanwashburne · Accepted solution]

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.