Help with subtotal

[Anonymous]

hi

im creating something with partial subtotal in a pivot table

all the columns are using sum(aggr) and those works great in the subtotal section..

ie -- sales  and cost

I have a few columns that is the product of the other 2..   total cost which is sales*cost...

the formula I have for that is =sales*cost.. works fine when filtered down..

but when showing all types of clothing.. it gives me random numbers in the subtotal..

but for the subtotal of sales and cost its correct cause it uses aggr?

any ideas on how to fix this?

thanks

[sunny_talwar]

Are you talking about 203057.5? How is that random? It is simply 250*813.23

May be try like this:

Sum(Aggr(Sales*Cost, 2bfasefg, 2bc))

[Anonymous]

sorry that formula.. triples my result..

not sure why

thanks

[sunny_talwar]

Would you be able to share a sample?

[Anonymous]

please see attached

not sure im trying everything

[sunny_talwar · Accepted solution]

Here you go:

=Sum(Aggr(Sum({<[2bfasefg],[2bc]>} sales) * Sum(Aggr(Sum(DISTINCT [2ldp]), [style])),[2bfasefg], [2bc]))

[Anonymous]

perfect

can I ask why aggr

I know there are tons of aggr explainations or definition.. but its kinda confusing..

would u be able to explain it?

but thank u

amazing fix

[sunny_talwar]

Aggr is nothing buy an imaginary table which is then used to perform the outer aggregation for you. In your case Aggr() function creates an imaginary table with

Dimensions

[2bfasefg]

[2bc]

Expression

Sum({<[2bfasefg],[2bc]>} sales) * Sum(Aggr(Sum(DISTINCT [2ldp]), [style]))

Now we have another Aggr() function within the expression, so QlikView creates another imaginary table

Dimension

[style]

Expression

Sum(DISTINCT [2ldp])

From here QlikView picks the Sum of Distinct values per style, 2bfasefg, 2bc and use that to multiple with the Sum of sales

Once that done, the final outside Aggr() is used to get the totals right. So it basically sums up the value on each row for you to show as a sub-total.

I am not sure if I explained this well enough or not. In my mind I know what it is doing, but it is difficult for me to put those thoughts in words.

Best,

Sunny