topic sum does not equal total in QlikView

sum does not equal total

jworthulf — Thu, 30 Aug 2018 17:11:43 GMT

I have two fields in a table and I have made selections in field one (text) values that have associated values in field 2. When I go, to sum up the values of the fields that I've selected using sum(field) and my total is incorrect.

Example

fields selected in a list box are B,C,D

Table 1

sum(Field2)

Field 1 Field 2

B 5

C 4

D 3

6 = Total

Re: sum does not equal total

vishsaggi — Thu, 30 Aug 2018 17:50:07 GMT

Can you share a screen shot or sample file you have this issue?

Re: sum does not equal total

jworthulf — Thu, 30 Aug 2018 19:31:11 GMT

I'm sorry this is all I have to offer.

Are there any data modeling issue that could possibly create a situation like this?

Re: sum does not equal total

vishsaggi — Thu, 30 Aug 2018 19:39:56 GMT

use a text object and do sum(Field2) then select values from field 1 and see if the sum totals up?

Re: sum does not equal total

vishsaggi — Thu, 30 Aug 2018 19:40:58 GMT

I cannot say anything unless you show me some screenshots or sample of what your data model is and your chart object you are trying to display your total.

Re: sum does not equal total

jworthulf — Thu, 30 Aug 2018 19:46:36 GMT

Vishwarath,

Here's an example of what I was looking at, instead of summing to 52 as the example shows the sum total was 39. What would create this type of issue?

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

Re: sum does not equal total

vishsaggi — Thu, 30 Aug 2018 19:54:51 GMT

This is what i can see when i open your test file and sum is calculated correctly which is 52? Is it somethin else i need to look at?

Re: sum does not equal total

jworthulf — Thu, 30 Aug 2018 19:58:00 GMT

what would create a sum of 39. I'm not able to replicate this in the example but it came up on test question today. Logically looking at the numbers I would assume that it did not sum the last number 13...but I'm not sure why or what would cause something like this to happen.

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

Re: sum does not equal total

vishsaggi — Thu, 30 Aug 2018 20:07:09 GMT

Can you screenshot the data model where you have this issue? Not sure why?

Re: sum does not equal total

jworthulf — Thu, 30 Aug 2018 21:29:30 GMT

Unfortunately, I'm not able to do that, the question is a hypothetical one at best as I do not have access to the all the script data that created the outcome of 39 vs 52.

What I showed you in my example above is what I was given with some multiple choice options in terms of the answer. I'm assuming there must be something in the design, data model, or UI that is creating this issue. The measure was very straightforward.

Thanks for taking a look.

Re: sum does not equal total

vishsaggi — Thu, 30 Aug 2018 23:22:52 GMT

Are there any duplicates or your expr is using any DISTINCT in your Sum() function in the front end worth a check.

Re: sum does not equal total

rohanmayekar — Fri, 31 Aug 2018 04:28:07 GMT

Looks like, while calculating the sum only distinct data values were considered.

in given example Numbers 8 & 9 have multiple occurrences. While summing if only distinct values are considered then sum value will come up as 35 instead of 52.

Re: sum does not equal total

BalaBhaskar_Qlik — Fri, 31 Aug 2018 09:22:00 GMT

There may be, a variable used in your measures expression, was not able to compute successfully. When you filter by that dimension as dimension did not affect the variable.

Try to amend the variable to ignore all filters, then the expression will be, able to compute even with filter applied.

Re: sum does not equal total

sagarjagga — Fri, 31 Aug 2018 10:15:38 GMT

are you using distinct function for a dimension in set expression of sum? it could be possible that you might have some duplicates among dimensions. for total option, you can use SUM instead of auto

Re: sum does not equal total

jworthulf — Fri, 31 Aug 2018 13:17:51 GMT

Sager,

Thank you for the input are you suggesting the sum of rows in the total mode, when you say "you can use SUM instead of auto"?

Thanks,

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

Re: sum does not equal total

jworthulf — Fri, 31 Aug 2018 13:29:51 GMT

Bala,

Can you show me an example?

Thanks,

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

Re: sum does not equal total

jworthulf — Fri, 31 Aug 2018 13:32:51 GMT

Rohan,

Thank you for the feedback, the formula in the example was sum(Field 5), but I do see where using sum (distinct Field 5) would give you a result closer to 39.

Thank you,

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

Re: sum does not equal total

wdchristensen — Fri, 31 Aug 2018 14:08:55 GMT

sum(num(Field2))

Re: sum does not equal total

jworthulf — Fri, 31 Aug 2018 15:00:27 GMT

William,

Thanks for the update,

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

w: upperlakesfoods.com<http://upperlakesfoods.com/>

Re: sum does not equal total

wdchristensen — Fri, 31 Aug 2018 15:51:32 GMT

In your example the sum is less than the expected total.Is that actually happening or is the total to large? If you data model is incorrect I would expect the total to be duplicating values making to actual total larger.

If you change sum to count, do you have to correct number of records?