Re: Pivot header grouping - Qlik Community

Mahamed_Qlik · ‎2021-08-10

Hi Experts,

I have pivot displaying as below:

I want its header to be group as below :

Note: All the columns are measures.

Regards,

Mahamed

Mahamed_Qlik · ‎2021-08-12

No solution yet?

Mahamed_Qlik · ‎2021-08-16

Come on experts.
Need your help.

Kushal_Chawda · ‎2021-08-16

@Mahamed_Qlik Please share screenshot of entire pivot with dimensions

Mahamed_Qlik · ‎2021-08-16

Mahamed_Qlik · ‎2021-08-16

I want to group first 2 column as 'Net Sales'
3-4 as ' Target Sales'
5-6 as ' Sales Return'

7-8 as 'Other'

9-10 as 'Target Achieved'

Kushal_Chawda · ‎2021-08-16

@Mahamed_Qlik try below

Create a two synthetic dimension and add it to column dimensions

1) Dimension for grouping

ValueList('Net Sales','Target Sales','Sales Return','Others','Target Achieved')

2) Dimension for Measure.

Note: Change measure name values according to your actual measure names.

ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2')

Now you can use below measure

=Pick(match(ValueList('Net Sales','Target Sales','Sales Return','Others','Target Achieved'),
'Net Sales','Target Sales','Sales Return','Others','Target Achieved'),
Pick(match(ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1','Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2'),
'Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2'),
Net Sales Mesure 1 Expression,Net Sales Mesure 2 Expression,0,0,0,0,0,0,0,0),

Pick(match(ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1','Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2',
'Target Achieved Measure 1','Target Achieved Measure 2'),
'Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2'),
0,0,Target Sales Mesure 1 Expression,Target Sales Mesure 1 Expression,0,0,0,0,0,0),

Pick(match(ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1','Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2',
'Target Achieved Measure 1','Target Achieved Measure 2'),
'Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2'),
0,0,0,0,Sales Return Mesure 1 Expression,Sales Return Mesure 2 Expression,0,0,0,0),

Pick(match(ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1','Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2',
'Target Achieved Measure 1','Target Achieved Measure 2'),
'Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2'),
0,0,0,0,0,0,Others Measure 1 Expression,Others Measure 2 Expression,0,0),

Pick(match(ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1','Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2',
'Target Achieved Measure 1','Target Achieved Measure 2'),
'Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure 1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2'),
0,0,0,0,0,0,0,0,Target Achieved Measure 1 Expression,Target Achieved Measure 2 Expression))

Go to chart properties->Add-on -> uncheck "Include zero values" option

.

Mahamed_Qlik · ‎2021-08-17

Thank you so much Khushal.
I will try this and will let you know.

Mahamed_Qlik · ‎2021-08-17

Hi Khushal

There is one more challenge I am getting now.

As you said in "2) Dimension for Measure"

Here my dimension names are again not plain text they are calculated. I mean my dimension labels are dynamic. then I am not able to put dynamic name inside your value list logic.

ValueList('Net Sales Measure 1','Net Sales Measure 2','Target Sales Measure1',
'Target Sales Measure 2','Sales Return Measure 1','Sales Return Measure 2','Others Measure 1','Others Measure 2','Target Achieved Measure 1','Target Achieved Measure 2')

For example, in above expression I have 'Net Sales Measure 1' this label as calculated as 'Net Sales' &'for'&'max(Year)

Kushal_Chawda · ‎2021-08-17

@Mahamed_Qlik you can use something like below

=valuelist('Net Sales for ' & $(=max(Year)),......)