topic Re: Partial sum in pivot table in QlikView

Partial sum in pivot table

Mon, 18 Mar 2013 21:32:42 GMT

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

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

Re: Partial sum in pivot table

MayilVahanan — Mon, 18 Mar 2013 21:38:51 GMT

For subtotal,try like this

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

hope it helps

Re: Partial sum in pivot table

hic — Mon, 18 Mar 2013 21:46:17 GMT

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

HIC

Re: Partial sum in pivot table

Mon, 18 Mar 2013 22:29:49 GMT

Thx a lot !

Re: Partial sum in pivot table

Tue, 26 Mar 2013 15:19:11 GMT

Is is possible to the same thing over two dimensions?

Sum(total <dimension1> <dimension2> Number)

This is not accepted. Is there an alternative?

Re: Partial sum in pivot table

hic — Tue, 26 Mar 2013 17:12:52 GMT

Sum(total <dimension1,dimension2> Number)

HIC