topic Re: Incorrect SUM value in QlikView

Incorrect SUM value

andrewmo — Tue, 14 Feb 2017 18:20:01 GMT

I have a value which is supposed to SUM room minutes, however I cannot get the value to line up with the manual calculation.

Here is my table model:

Here are the individual values for the room minutes:

The sum of this is 1880

Here is what I get when using the SUM function:

The value represented from this formula is 1970...?

Why is the SUM value incorrect?

Re: Incorrect SUM value

sunny_talwar — Tue, 14 Feb 2017 18:25:16 GMT

What you are seeing is the distinct values within Room Minutes field, but there might be multiple values for the same number. Sum([Room Minutes]) will sum all values. Not a good idea, but Sum(DISTINCT [Room Minutes]) will give the sum of distinct values within Room Minutes field. Is that what you want?

Re: Incorrect SUM value

Anil_Babu_Samineni — Tue, 14 Feb 2017 18:31:34 GMT

May be use this in text box and then see in Straight/Pivot

Sum([Room Minutes])

Sum(TOTAL [Room Minutes])

Sum(Distinct [Room Minutes])

Re: Incorrect SUM value

Anonymous — Tue, 14 Feb 2017 18:47:52 GMT

I think "90" is not part of the selection "HILLCREST MAIN OR"

Re: Incorrect SUM value

andrewmo — Tue, 14 Feb 2017 19:00:43 GMT

I selected each ID manually and it appears the 90 minute case is being summed twice. Not sure how or why...?

Re: Incorrect SUM value

Anonymous — Tue, 14 Feb 2017 19:03:41 GMT

try Concat([Room Minutes],' | ')

as expression for debugging

Re: Incorrect SUM value

aarkay29 — Tue, 14 Feb 2017 19:28:09 GMT

Add Dimensions to the straight table from the tables CALBLOCK and STGII, you will be able to see where the data is duplicating. If still cannot find the values on which the expression is duplicating try adding dimensions from CASES table as well.

Re: Incorrect SUM value

swuehl — Tue, 14 Feb 2017 20:24:58 GMT

It seems to me like your issue can be explained only looking at the CASES table, since both the dimension and expression field originate from that table.

What is the primary key of that table? Is it [Case No], i.e. is there only a single record per case no?

Or is it [Log ID]? Or a combination of fields?

Try to add the primary key to your straight table as dimensions.

Another way to investigate could be to check your data source and script. Are you using JOIN in your script related to table CASES? A JOIN may unintentionally duplicate records.

After all, maybe the value shown is indeed correct, so we might need also to discuss the way you've calculated the sum manually. How have you done it? As Sunny pointed out, a list box or table box will only show distinct combinations of values, it will not show duplicate records or sets of identical values.

Re: Incorrect SUM value

janseng89 — Thu, 06 Aug 2020 16:39:33 GMT

Hi,

With this debug method i've noticed my formula cut off after 68 seperates; yet the straight table has 75 rows; any clue what the cause is in this situation?