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,690 Views

30 Replies

jworthulf

Creator

2018-09-04
09:07 AM

Author

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

William,

Thank you for the feedback, the total appears of 39 appears to be wrong as all the values under field 3 are selected (green) and if you manually add up the numbers 52 appears to be correct. The only other thing I will add is the sort order of field 3 in the table was different vs the list box for field 3, I did not show this in the example.

Is there anything that could be going on with the values under F or a combination of B&E or A&E as the value difference is 13?

Thanks,

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

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

711 Views

sagarjagga

Creator

2018-09-04
09:29 AM

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

Yes John,

Are you using distinct function in your set analysis(to find out sum)?

710 Views

swuehl

MVP

2018-09-04
10:10 AM

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

You are probably experiencing an issue caused by the difference between a sum-of-rows and expression total evaluation of your expression in the total line.

This may rise up e.g. with a little more complex data model:

Facts:

LOAD * INLINE [

Dim, Key

A, 1

A, 2

B, 1

C, 2

D, 3

E, 1

E, 2

E, 3

F, 4

];

Numbers:

LOAD * INLINE [

Key, Number

1, 1

2, 2

3, 3

4, 4

];

Then creating a straight table with dimension Dim and two expressions (both = Sum(Number) but with different total mode):

Dim | sum of rows | expression total |
---|---|---|

B | 1 | 1 |

C | 2 | 2 |

A | 3 | 3 |

D | 3 | 3 |

F | 4 | 4 |

E | 6 | 6 |

19 | 10 |

jworthulf

Creator

2018-09-05
10:39 AM

Author

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

Stefan,

Can you walk me thru the expression total and show me how the system went about getting 10.

Thanks,

John

711 Views

vishsaggi

Champion III

2018-09-05
10:55 AM

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

What stefan pointing to was Total Mode in expression tab like below:

Here the first expr total mode is Sum of rows. So it will sum the rows as is.

The 2nd expr Total mode is Expr Total. So here it is taking the sum of Distinct Number value and then it sums up. Hence 10 in total.

swuehl

MVP

2018-09-05
11:02 AM

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

It's in detail explained in

711 Views

jworthulf

Creator

2018-09-05
11:09 AM

Author

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

Vishwarath,

I’m assuming the total expression was summing just the totals and the distinct values are actually totals

Expression Total:

A B C D E F

Total 1 2 3 4 = 10

1 1

2 2

3

Sum of Rows:

A B C D E F

Total 3 1 2 3 6 4 =19

1 1

2 2

3

Thanks

John

711 Views

vishsaggi

Champion III

2018-09-05
11:52 AM

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

Yes.

711 Views

jworthulf

Creator

2018-09-06
09:20 AM

Author

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

Vishwarath,

Thank you for letting me know this as I've only placed a couple posts.

Also thank you for all your help.

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

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

711 Views

jworthulf

Creator

2018-09-06
09:21 AM

Author

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

Stefan,

Thanks for the reminder I really appreciate all the hlep.

Have a great weekend.

John Worth

BI Data Analyst

Upper Lakes Foods

p: 800.879.1265 ext 4278

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

711 Views