topic Re: Create a column in Pivot Table in QlikView

Create a column in Pivot Table

katetsan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I'm wondering if there's possible to create a column in pivot table?

The format is like below, Month is as the dimension in the pivot table, and I would like to create extra column called 'MoM'.

Is there any way to make this happen?

Item	Jan	Feb	Mar	MoM
A	100	110	90	-20
B	150	160	130	-30
C	80	90	60	-30
D	50	60	100	40

Thanks a lot

Re: Create a column in Pivot Table

devarasu07 — Tue, 03 Oct 2017 06:50:59 GMT

Hi,

try like this

Dim:

Item

ValueList('Jan','Feb','Mar','MoM')

Expression

if(ValueList('Jan','Feb','Mar','MoM')<>'MoM',sum({$<Month={'Mar'}>}Amt),sum({$<Month={'Mar'}>}Amt)-sum({$<Month={'Feb'}>}Amt))

P.S: based on you Calendar data you can change the Month/Date value in above expression.

Thanks,Deva

Re: Create a column in Pivot Table

Sergey_Shuklin — Tue, 03 Oct 2017 07:08:00 GMT

Hello, Kate!

You can also use Pick() function, but first you need to improve your script with wthis load:

Load * inline

[dim

2];

after that create a pivot table with two dimensions:

Item

and

Pick(dim,month_field,'MoM')

As expression use this:

Pick(dim,sum(val),sum({<cur_month>}val)-sum({<prev_month>}val))

Re: Create a column in Pivot Table

pradosh_thakur — Tue, 03 Oct 2017 07:42:18 GMT

as per your requirement and if i understood it correctly you want to create a dynamic/extra column that will show the subtraction of two column march and feb in this case which can be pre populated or else calculated

1:create column MOM

2: in expression type column name1 within [ ] - column name 2 within [ ]

something like [column1]-[column2] or [Mar] - [Feb]

share the app if it doesn't work for you.

regards

Pradosh

Re: Create a column in Pivot Table

katetsan — Tue, 03 Oct 2017 08:00:08 GMT

Hi Devarasu,

Thanks for your reply.

What if there is more than three months, is it possible to use the field name like "Month" instead of individual month (such as Jan, Feb, and Mar)?

Thanks