topic Re: Use subtotal in expression in pivot table in QlikView

Use subtotal in expression in pivot table

Anonymous — Wed, 03 Aug 2016 19:35:37 GMT

Hi,

I have this pivot table:

What I want to do in the second column is: value of Jerarquia 2 / Total value of Jerarquia 1

For example:

Jerarquia 1 subtotal = 30 then the second expression should be = 10 / 30 ==> 0,33..

...

Jerarquia 2 subtotal= 20 then the second expression should be = 10 / 20 ==> 0,5

Who can I set the expression to take the subtotal of the first dimension and use it. Can I do that with a qlikview's funtion?

Thanks,

Regards

Re: Use subtotal in expression in pivot table

vishsaggi — Wed, 03 Aug 2016 19:40:51 GMT

Hello Leandro,

Can you post a sample app to work on?

Thanks,
V.

Re: Use subtotal in expression in pivot table

Anonymous — Wed, 03 Aug 2016 19:47:55 GMT

Here is the app

Re: Use subtotal in expression in pivot table

sunny_talwar — Wed, 03 Aug 2016 20:05:14 GMT

Try this:

Sum(Valor)/Sum(TOTAL <Jerarquia1> Valor)

Re: Use subtotal in expression in pivot table

Anonymous — Wed, 03 Aug 2016 20:11:58 GMT

Nice!

It's works!, thaks.

Can you tell me what you do when in the exp you put <Jerarquia1>? I tryed setting in the expression' set analisys {<Jerarquia1=>} but that doesn't work.

Re: Use subtotal in expression in pivot table

sunny_talwar — Wed, 03 Aug 2016 20:15:07 GMT

TOTAL <Jerarquia1> is telling the expression to Total as per Jerarquia1 dimension. To see what it is doing, create a new expression in your table using the expression -> Sum(TOTAL <Jerarquia1> Valor)

Note: TOTAL <FieldName> is different from {<FieldName>}. Second one (set analysis) will ignore selection in FieldName, while the first one will show the total by the listed dimension

Re: Use subtotal in expression in pivot table

Anonymous — Wed, 03 Aug 2016 20:47:24 GMT

Thanks! It's usefull