Incorrect Sum value; expression

[janseng89]

Hi all,

I've created an easy QV file to cross check multiple data sources; in the data set I want to sum the total value of the [net price]; 

By expressions
SUM([Net amount]) I do receive an amount of 1.161M; yet if I export the data set and sum it via exel =SUM(A:A) the value is 1.2M any clue how this may be caused.

Does anyone know the issue>?

** Additional information

I've added a filter on a small data reason; and 

By debug logic: Concat([Net amount],' | ')  I only find for one  68 comma seperators; instead of the 75 lines; the difference amount * 7 lines is the different amount; perhaps this does cause the issue

[aniketsr]

Please check if the number of rows extracted in excel do not exceed the excel row limit.

[janseng89]

Thank you for the suggestion!

I've reached  496.326 lines in Excel; also if this was the cased I would have assumed the value in excel would be less than in QV;

Any other suggestions perhaps?

[aniketsr]

Just a thought without looking at your data model .

Please check if you can write something like this :

Sum( distinct ([Net Value]))

or 

sum(Total ([Net Value]))

[janseng89]

Hi,

Tried both expressions; both without success;

Any other suggestions?
Can I perhaps share information?

[aniketsr]

Yes please , if you can mask the data and send across a sample qvw will be great.

[janseng89]

See attachment; can u verify?

[aniketsr · Accepted solution]

The data model needs some modification .

Please create a composite key for tracking number and date between table data and the other two excel data .

secondly , also concatenate the two excels as the structure of both the excels is same .

finally you will have only Data table and one  concatenated excels table in the model.

(The issue that you are facing is due to null invoice dates in the data which is caused due to synthetic key.)

[janseng89]

Thank you this resolved the issue