Sum of calculated values in coloum

[nm02]

Hello,

I have a table with the following structure (Screenshot 1):

• [Abbreviation] is just an ID to generate the rows
• [Factor1], [Factor2], ..., [Factor8] and [Count] are just numbers I want to add up in 'Sum'
• Column M ('Sum') is a measure that calculates:

  • ([Factor1] + [Factor2] + ... + [Factor8])*Sum([Count])

• Because of the table structure, it calculates everything per row (as I want to)

What I want:

• Sum of every row of column M 'Sum'
  • As a formula in Excel, you just calculate like this:

    • Sum(M2:M13)

• The result should be 520€ as you can see in Screenshot 1, cell M14
• Pseudo code:

  • Sum of each row of [Sum]

What I get:

• I just use the TOTAL function of the table where I get the value in Screenshot 2 (1,3k€)
  • Alternatively, I want to use an KPI where I display the measure 'Sum' (I get the same result: 1,3k€)
• I think that Qlik calculates like this:

  • (Sum([Factor1] + [Factor2] + ... + [Factor8]))*Sum([Count])​

    
    
    • This is obviously not what I want as it doesn't calculate each individual row, it just takes the sum of all factors in the whole dataset and multiplies times the sum of [Count]
• The difference between the real calculation of Excel and the "calculation" of Qlik grows as I use more IDs which is a big problem

Screenshot 1 ('Excel Sum'):

Screenshot 2 ('Qlik Sum'):

Thanks in advance for every help.

[rwunderlich · Accepted solution]

By default Qlik calculates the total row by recalculating the expression over all the data. If you want the total to be a straight sum of rows you can change the chart Data Totals function dropdown from "Auto" to "Sum". 

I believe your expression is written incorrectly for what Qlik expects.  "Sum(a + b)" and "Sum(a) + Sum(b)" are the same if there is only one row. When you get to the total row, you have multiple rows.

I suggest you write the expression as:

Sum(RangeSum(Factor1, Factor2,...,Factor8) + Count)

That should work for both the rows and the Total. It will also work if ever rolled up to multiple rows using another dimension. 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[nm02]

Changing the TOTAL chart data to Sum was the missing key I needed.

Now it sums up all the rows as I wanted.

Thanks for your help!