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

[varshavig12]

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.

[Kushal_Chawda]

Can you show your Pick & match condition?

perhaps you can directly assign Sum for both AB & CD

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

[varshavig12]

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

QlikView will treat them same

[varshavig12]

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

I am using KPI for sorting purpose.

[varshavig12]

I made a demo:

My script:

LOAD * INLINE [

    no, name

    2, a

    3, b

    5, c

    6, d

];

LOAD * INLINE [

KPI

1

2

3

];

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))

[sunny_talwar · Accepted solution]

How about this?

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

[varshavig12]

Thank you so much, Sunny.

A big thanks to you and this community.

[varshavig12]

Thank you for trying to solve this problem.