topic Sub total pivot table in QlikView

Sub total pivot table

Fri, 28 Mar 2014 14:29:39 GMT

Hi everyone,

I am a beginner. I would like to get something like this :

Dim 1 DIm2 Exp1 Exp2

A j 2 14

A d 5 14

A e 7 14

B t 1 5

B r 4 5

I don't know how to get the Exp2. I would like to do a sum without taking into account the second dimension.

I tried an expression like sum ({<Dim2=>} value) but this doesnt work.

thank you for your help

Re: Sub total pivot table

Nicole-Smith — Fri, 28 Mar 2014 14:30:23 GMT

sum(TOTAL <Dim1> value)

Re: Sub total pivot table

alexandros17 — Fri, 28 Mar 2014 14:31:16 GMT

Try this

sum(aggr(sum(value), Dim1))

Re: Sub total pivot table

MK_QSL — Fri, 28 Mar 2014 14:43:14 GMT

Script side...

Data:

Load * Inline

[

Dim1, Dim2, Exp1

A, j, 2

A, d, 5

A, e, 7

B, t, 1

B, r, 4

];

Left Join (Data)

Load

Dim1,

SUM(Exp1) as Exp2

Resident Data

Group By Dim1;

=====================

You expression Exp1 could be Sum(Sales)

Re: Sub total pivot table

Fri, 28 Mar 2014 15:44:44 GMT

Thank you Nicole, it works well.

Now, I would like to have a 3rd expression which is exp1/exp2 (so the percentage). I did the expression exp1 / the expression exp 2 and it works but I'm just wondering if there was another way to do it (a shorter or simpler way) ?

Thanks again.

Re: Sub total pivot table

MK_QSL — Fri, 28 Mar 2014 16:14:42 GMT

Column(1)/Column(2)