topic Re: Pivot table calculation in QlikView

Pivot table calculation

Thu, 10 Jul 2014 14:31:39 GMT

Hello,

Please see attached XLS file for an example I would like to build in QV. Creating the "base" pivot table is easy but getting stuck in trying to add blue shaded column and rows.

Any idea how to achieve this?

Thanks you!

Re: Pivot table calculation

hic — Thu, 10 Jul 2014 14:47:46 GMT

The rightmost column can be created using a formula like

If(Dimensionality()=2,

Count(X)/Count(total <Dim1> X),

Count(X)/Count(total X))

But the bottom rows are trickier. For that you need to change the data model.

HIC

Re: Pivot table calculation

hic — Thu, 10 Jul 2014 15:04:07 GMT

I just realized that you can get the bottom rows doing the following:

Add the following Load statement to your script:

Load RecNo()-1 as IsTotal Autogenerate 2;

Then you can define your Dim1 in the pivot as (Calculated Dimension):

=Aggr(Only(If(IsTotal,'All Types',Dim1)),Dim1,IsTotal)

HIC

Re: Pivot table calculation

Thu, 10 Jul 2014 15:15:10 GMT

Hi,

Is a specific chart but I do it using 2 charts (Pivot Table and Straight Table), and change origina data see XLS file.

I hope it will usefull for you. Regards.

Re: Pivot table calculation

Thu, 10 Jul 2014 19:08:20 GMT

Hi Henric,

Thank you for your response. Not quite sure I am following your train of thought. Can you please explain your approach?

Marko

Re: Pivot table calculation

Thu, 10 Jul 2014 19:09:47 GMT

Hi Oswaldo,

Thanks for your response. I was thinking of adding straight table but would love to fulfil business requirement, if possible, to have it all displayed in the pivot chart. This will be my last resort.

Marko

Re: Pivot table calculation

hic — Fri, 11 Jul 2014 06:15:59 GMT

The 'All Types' lines that you want at the bottom cannot be created without a change in the data model. So that's why you should add a small table in the load script:

Load RecNo()-1 as IsTotal Autogenerate 2;

Then I use this table together with the Dim1 to create the 4 dimensional values that you want (1,2,3,All types) by using a calculated dimension:

=Aggr(Only(If(IsTotal,'All Types',Dim1)),Dim1,IsTotal)

HIC