topic Re: Difference between 2 pivot fields in QlikView

Difference between 2 pivot fields

danielact — Tue, 11 Dec 2012 16:33:22 GMT

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 — Tue, 11 Dec 2012 17:13:26 GMT

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 — Tue, 18 Dec 2012 14:31:51 GMT

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

kaushiknsolanki — Mon, 24 Dec 2012 07:18:05 GMT

HI,

Could you explain what calculation dimension you are using.

If possible post QVW file with sample data.

Regards,

Kaushik Solanki

Tue, 25 Dec 2012 10:22:01 GMT

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.