topic Re: Pivot Table Totals in QlikView

Pivot Table Totals

Wed, 25 Nov 2020 16:16:04 GMT

I have read a lot of threads about the pivot table totals but can't seem to find a similar post to what I require (and am also still learning).

I have a pivot table with two dimensions, Type and FY, where I have type on the left and FY across the top (pictured below).

There are five expressions within the table (the avg and count rows). I just want to calculate the difference of these rows to come up with the % variance as demonstrated below.

Appreciate any feedback or suggestions to construct this better to give me a result similar to the above. With Thanks B.

Re: Pivot Table Totals

vardhancse — Fri, 19 May 2017 08:26:49 GMT

Hi,

in Variance expression:

=(column(2)-Column(1))/column(2)*100

**column(1)=2015/16

**column(2)=2016/17

Re: Pivot Table Totals

Mon, 22 May 2017 22:04:12 GMT

When I try this I get the following result:

I ideally want the % to sit beside the 2016/17 Column...

Appreciate any thoughts... thanks

Re: Pivot Table Totals

Digvijay_Singh — Mon, 22 May 2017 23:44:36 GMT

There is a workaround to create new column like this.

Step1 - Add one inline island table in the data table as -

Dim:

LOAD * Inline [

Dim

];

Step2 -

In the chart use your FY dimension as -

=Pick(Dim,[Sold Year],'Variance') //So Dim =1 will act as usual FY values and Dim=2 for your new variance column.

Use your expressions like below, just an example-

Pick(Dim,Sum([# Sales Amount]),Sum({<[Sold Year]={2012}>}[# Sales Amount])-Sum({<[Sold Year]={2011}>}[# Sales Amount]))

Sample chart using above method -

Re: Pivot Table Totals

vardhancse — Tue, 23 May 2017 04:40:14 GMT

Can share any sample app