topic Re: Help with subtotal in QlikView

Help with subtotal

Anonymous — Wed, 14 Dec 2016 00:36:52 GMT

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

Re: Help with subtotal

sunny_talwar — Wed, 14 Dec 2016 02:00:18 GMT

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

Re: Help with subtotal

Anonymous — Wed, 14 Dec 2016 02:29:31 GMT

sorry that formula.. triples my result..

not sure why

thanks

Re: Help with subtotal

sunny_talwar — Wed, 14 Dec 2016 09:14:31 GMT

Would you be able to share a sample?

Re: Help with subtotal

Anonymous — Wed, 14 Dec 2016 16:49:53 GMT

please see attached

not sure im trying everything

Re: Help with subtotal

sunny_talwar — Wed, 14 Dec 2016 16:59:52 GMT

Here you go:

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

Re: Help with subtotal

Anonymous — Wed, 14 Dec 2016 23:55:08 GMT

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

Re: Help with subtotal

sunny_talwar — Thu, 15 Dec 2016 03:18:26 GMT

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