Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: **REGISTER NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: sum does not equal total

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

jworthulf

Creator

2018-08-30
01:11 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

sum does not equal total

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

3,738 Views

30 Replies

vishsaggi

Champion III

2018-08-30
07:22 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

712 Views

rohanmayekar

Partner - Contributor III

2018-08-31
12:28 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

712 Views

balabhaskarqlik

Master

2018-08-31
05:22 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

712 Views

sagarjagga

Creator

2018-08-31
06:15 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

jworthulf

Creator

2018-08-31
09:17 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

jworthulf

Creator

2018-08-31
09:29 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

712 Views

jworthulf

Creator

2018-08-31
09:32 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

712 Views

wdchristensen

Specialist

2018-08-31
10:08 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

sum(num(Field2))

712 Views

jworthulf

Creator

2018-08-31
11:00 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

712 Views

wdchristensen

Specialist

2018-08-31
11:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

713 Views