Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
jworthulf
Creator
Creator

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

30 Replies
jworthulf
Creator
Creator
Author

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

sagarjagga
Creator
Creator

Yes John,

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

swuehl
MVP
MVP

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
B11
C22
A33
D33
F44
E66
19 10
jworthulf
Creator
Creator
Author

Stefan,

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

Thanks,

John

vishsaggi
Champion III
Champion III

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.

SumOfRows.PNG

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.

ExprTotal.PNG

swuehl
MVP
MVP

It's in detail explained in

Totals in Charts

jworthulf
Creator
Creator
Author

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

vishsaggi
Champion III
Champion III

Yes.

jworthulf
Creator
Creator
Author

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

jworthulf
Creator
Creator
Author

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