Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
janseng89
Contributor
Contributor

Incorrect Sum value; expression

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

 

 

 

1 Solution

Accepted Solutions
aniketsr
Creator
Creator

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.)

 

View solution in original post

8 Replies
aniketsr
Creator
Creator

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

janseng89
Contributor
Contributor
Author

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
Creator
Creator

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
Contributor
Contributor
Author

Hi,

 

Tried both expressions; both without success;

 

Any other suggestions?
Can I perhaps share information?

aniketsr
Creator
Creator

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

janseng89
Contributor
Contributor
Author

See attachment; can u verify?

aniketsr
Creator
Creator

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
Contributor
Contributor
Author

Thank you this resolved the issue