Difference between 2 pivot fields

[danielact]

I have a pivot table that looks something like this:

Label     Type1     Type2     Type3

A          10          15          20

B          50          150          95

I want to add a 3rd row which will be B-A (B will always be larger), so it should look like this:

Label     Type1     Type2     Type3

A          10          15          20

B          50          150          95

B-A       40          135          75

Any idea how to set this up?

[christian77]

Hi:

Create a table with Type as dimension.

Then create 3 columns called A, B, A-B and expressions sum(A), sum(B) and sum(A)-sum(B) or simply column(1)-column(2).

Work on your expressions, these are very simple.

[danielact]

The problem is that I'm using calculated dimensions, not just values called A & B.

[kaushiknsolanki]

HI,

     Could you explain what calculation dimension you are using.

     If possible post QVW file with sample data.

Regards,

Kaushik Solanki

[#5]

HI

FIRSTLY IN YOUR SCRIPT WRITE

LOAD label1,

     type1,

     type2,

     type3

FROM

(ooxml, embedded labels, table is Sheet1);

LOAD * INLINE [

   label1

    B-A

];

THEN ADD PIVOTE CHART ---> ADD DIMENSION   label1

                                         --> IN EXPRESSION  WRITE

IF(label1='B-A', (Above(type1)-Above(Above(type1))),type1)       FOR TYPE 1

IF(label1='B-A', (Above(type2)-Above(Above(type2))),type2)        FOR TYPE 2

IF(label1='B-A', (Above(type3)-Above(Above(type3))),type3)        FOR TYPE 3

HOPE IT HELPS YOU.