Total of Rows should be showing in Subtotal of Pivot Table in Qlikview Chart

[K_Pandey]

Here, in Column L which is getting calculated by expression in Pivot Table Chart expression. the subtotal is coming as expression calculation what I need is Column "M", Reference to column "N" which is showing difference of L-M. How can I achieve Row total of Sub Product "B" in Subtotal of Product "A" (Red font cells in Col L & M)

Attached is Excel Sheet is for reference. Thanks in Advance for all the help.

[marcus_sommer]

I think you need the TOTAL statement within your aggregations to ignore all object-dimensions respectively to consider only the specified ones. This means something like this:

sum(TOTAL MyValue)

sum(TOTAL <MyDim1, MyDim2> MyValue)

[K_Pandey]

output is 0 for the same.. Column L is calculation of "=(E-H)*C"

[marcus_sommer]

Did you adapt the logic to your fields?

[K_Pandey]

Hi, Yes I am trying to use the same but not working maybe I am missing something. Attaching demo QVW file. Would be helpful if you could refer last 4 columns

[marcus_sommer · Accepted solution]

Beside the TOTAL feature to ignore some dimensionality you will also need an aggr() wrapping of your calculations to use the sum/avg of the rows within the partial-sum calculations. This may look like:

sum(aggr((sum(E) - sum(H)) * sum(C), [Sub Product]))

By applying only simple sum(Field) or count(Field) the partial sums will always be working. But as far as you include any conditions in the expressions and/or combining several calculations for any kind of rates you need to define which kind of weighted result is the one which you want. And therefore the above shown aggr() to define the dimensional context for the calculation. 

[K_Pandey]

Here is catch I ran into .. When the Ratios fields are derived in from expression it's not working. only works fine when all the fields are coming from Raw file itself. attached is file for reference. Please refer last 3 columns for all scenarios

[marcus_sommer]

This sounds that a single aggr() layer isn't enough else that you may need a nested approach, like:

sum(aggr(
   sum(aggr(sum(F1) / sum(F2), Dim1)) * sum(aggr(sum(F3) / sum(F4), Dim1)),
Dim1))