topic Re: Pivot Table in QlikView

Pivot Table-- Calculated Dimension--Customize same name to be treated different.--urgent

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

Hello Everyone,

I am just stuck here.

Below is the screenshot for better understanding, Where table 1 is my current output and table 2 is the expected one.

AB and CD are calculated dimensions using pick-match.

Now, what I want is, field AB and CD should have same name = 'sum'.

I tried using If in calculated expression but in that case QlikView treat them as 1 field and not separate.

I am able to do this in straight table using expression and then hiding the name column, but just because I want horizontal month field I have to use pivot.

Can anyone help me with this.

Thank you in advance.

Re: Pivot Table

Kushal_Chawda — Fri, 10 Jun 2016 06:45:15 GMT

Can you show your Pick & match condition?

perhaps you can directly assign Sum for both AB & CD

Pick(Match(Field,'A','B'),'Sum','Sum')

Re: Pivot Table

varshavig12 — Fri, 10 Jun 2016 06:50:53 GMT

If I use this --> Pick(Match(Field,'A','B'),'Sum','Sum')

QlikView will treat them same

Re: Pivot Table

varshavig12 — Fri, 10 Jun 2016 06:55:57 GMT

=pick(match(KPI,1,2,3),'AB','CD',name)

I am using KPI for sorting purpose.

Re: Pivot Table

varshavig12 — Fri, 10 Jun 2016 07:10:59 GMT

I made a demo:

My script:

LOAD * INLINE [

no, name

2, a

3, b

5, c

6, d

];

LOAD * INLINE [

KPI

];

LOAD * INLINE [

name, sales, month

a, 10, Jan

b, 20, Jan

c, 10, Jan

d, 20, Jan

a, 10, Feb

b, 20, Feb

c, 10, Feb

d, 20, Feb

];

Dashboard:

Charts type: Pivot

Dimension:

=pick(match(KPI,1,2,3),'AB','CD',name)

month

Expression:

if(isnull(name),100,sum(sales))

for sorting:

Expression:

if(KPI=1,1,

if(KPI=2,4,

no))

Re: Pivot Table

sunny_talwar — Fri, 10 Jun 2016 15:14:12 GMT

How about this?

=Pick(Match(KPI,1,2,3),Dual('Sum', 1),Dual('Sum', 2),name)

Re: Pivot Table-- Calculated Dimension--Customize same name to be treated different.--urgent

varshavig12 — Mon, 13 Jun 2016 03:04:08 GMT

Thank you so much, Sunny.

A big thanks to you and this community.

Re: Pivot Table-- Calculated Dimension--Customize same name to be treated different.--urgent

varshavig12 — Mon, 13 Jun 2016 03:04:58 GMT

Thank you for trying to solve this problem.