Partial sum in pivot table

[#1]

Hello,

sorry if i overlooked.

i have 2 dimensions (product and canal)

I would like to divide :

the quantity for each product and each canal : easy double aggr :  sum(aggr(Quantity,Canal,Product)) 

by

all the quantity sold on this canal. I thought only one aggr : sum(aggr(Quantity,Canal)) (the equivalent of the partial total) but it doesn't work properly. sometimes 0 is displayed sometimes it's an incomprehensible number...

How can i get this "subtotal" ? am I in a wrong way ? maybe use the function only ?

Canal	1	2	3
Product
A	1 /2	2 /5	1 /1
B	0 /2	3 /5	0 /1
C	1 /2	0 /5	0 /1
subtotal	2	5	1

Ce message a été modifié par: tangred83

[MayilVahanan]

HI

For subtotal,try like this

=If(isnull(rowno()) or rowno() = 0, your subtotal expression, your expression)

hope it helps

[hic · Accepted solution]

You should not use Aggr() here. Instead you should use the "total" qualifier inside the Aggregation function. The partial sums of the column (2, 5 and 1) can be obtained through

     Sum(total <Canal> Number)

See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-u...

HIC

[#4]

Thx a lot !

[#5]

Is is possible to the same thing over two dimensions?

Sum(total <dimension1> <dimension2> Number)

This is not accepted. Is there an alternative?

[hic]

Sum(total <dimension1,dimension2> Number)

HIC