Pivot table calculation

[#1]

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!

[hic]

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

[hic]

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

[#4]

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.

[#5]

Hi Henric,

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

Marko

[#6]

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

[hic · Accepted solution]

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