topic Re: How to add column to a pivot table in QlikView

How to add column to a pivot table

Thu, 03 Apr 2014 08:51:26 GMT

Hi,

I made a pivot table like below (without the yellow column)

Now I want add the yellow column which value is the difference between last two month (in this case,201404 and 201403) to the right of the pivot.

Can Qlikview do this?

Thank you very much for your help.

Best regards

Wei

Re: How to add column to a pivot table

alexandros17 — Thu, 03 Apr 2014 08:55:31 GMT

Add a new expression that computes what you need

Re: How to add column to a pivot table

richard_pearce6 — Thu, 03 Apr 2014 11:03:08 GMT

You can use the above function, see attached qvw for an example

Regards

Richard

QlikCentral.com

Re: How to add column to a pivot table

Thu, 03 Apr 2014 11:51:46 GMT

Hi,

Try using SecondaryDimensionality , attached is the qvd.

Thanks & Regards,

MVV Satish.

Re: How to add column to a pivot table

its_anandrjs — Thu, 03 Apr 2014 11:58:13 GMT

Yes you can add its depends on your expression

201404 - 201403 = diff

in another column

Re: How to add column to a pivot table

nizamsha — Thu, 03 Apr 2014 12:25:26 GMT

COLUMN(2)-COLUMN(1) WILL GIVE THE DIFFERENCE IF U WANT THE COLOR MEAN FOR THE DIFFERENCE ROM MEAN U HAVE TO GIVE IN EXPRESSION BACKGROUND

IF(COLUMN(2)-COLUMN(1)>0,YELLOW(),WHITE())

Re: How to add column to a pivot table

Fri, 04 Apr 2014 02:16:25 GMT

Hi,

That's what I want. Thank you very much.

May I ask 2 more questions?

I saw you use this expression to define value:

=if(SecondaryDimensionality()=0,sum({<[FQ]={ $(vMaxFQ)}>}[Value])-sum({<[FQ]={ $(vMaxPrevious)}>}[Value]),

sum(Value))

I don't understand $(vMaxFQ) and $(vMaxPrevious).

And in my case qty,amt,unit is already defined by sum function, can I just replace [Value] by an expression sth like sum(qty)?

Re: Re: How to add column to a pivot table

Fri, 04 Apr 2014 09:02:07 GMT

Hi, Satish

I modified your qvw to test how it works when there are more values in data area.

And I got the result as below.

FQ1 and FQ2 are missed in this pivot and all FQ3 value are negative.

Is there any restriction when using function SecondaryDimensionality()?

Best regards

Wei

Re: How to add column to a pivot table

Mon, 07 Apr 2014 05:34:20 GMT

Hi,

vMaxFQ and vMaxPrevious are variables to get the max quarter and previous quarter, in your case you can create variables for months.

Yes you can replace Value with qty

Best Regards,

MVV Satish.

Re: How to add column to a pivot table

Mon, 07 Apr 2014 05:42:18 GMT

Hi,

Move the Values below FQ in the pivot, it should work.

Best Regards,

MVV Satish.

Re: How to add column to a pivot table

Tue, 08 Apr 2014 01:30:26 GMT

Yes,when I move value below FQ, it works.

So I can't use SecondaryDimensionality() when values at right of country?

Because besids diff, I still have one column which will use the value of diff, is it possible?

Best regards,

Wei