topic Re: Incorrect Sum value; expression in QlikView

Incorrect Sum value; expression

janseng89 — Thu, 06 Aug 2020 17:01:30 GMT

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

Re: Incorrect Sum value; expression

aniketsr — Thu, 06 Aug 2020 17:02:21 GMT

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

Re: Incorrect Sum value; expression

janseng89 — Thu, 06 Aug 2020 17:05:22 GMT

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?

Re: Incorrect Sum value; expression

aniketsr — Thu, 06 Aug 2020 17:09:09 GMT

Just a thought without looking at your data model .

Please check if you can write something like this :

Sum( distinct ([Net Value]))

sum(Total ([Net Value]))

Re: Incorrect Sum value; expression

janseng89 — Thu, 06 Aug 2020 17:11:45 GMT

Hi,

Tried both expressions; both without success;

Any other suggestions?
Can I perhaps share information?

Re: Incorrect Sum value; expression

aniketsr — Thu, 06 Aug 2020 17:13:31 GMT

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

Re: Incorrect Sum value; expression

janseng89 — Thu, 06 Aug 2020 17:54:33 GMT

See attachment; can u verify?

Re: Incorrect Sum value; expression

aniketsr — Thu, 06 Aug 2020 17:43:01 GMT

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

Re: Incorrect Sum value; expression

janseng89 — Thu, 06 Aug 2020 17:54:13 GMT

Thank you this resolved the issue