Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
RanOuerg
Creator
Creator

Change mesures name in a pivot table

Hello ,

I have a pivot table with:

Dimension: Matricule , Nom

Column: Type( Frais or Gain)

Mesures : GH,GM,SIP

RanMH_0-1710771378490.png

i want to change  the name of the mesures based on the value of the column "Type"

Exemple If Type='Gain' and mesure = GH then the mesure name will be "Gain GH"

RanMH_1-1710771462274.png

I used this formula as a name of the mesure : if(wildmatch(Type,'Gain'),'Gain GH', 'Frais GH') but it's not working it gives the same value everywhere 

Is there a way to do this ?

Thank you in advance.

 

 

 

1 Solution

Accepted Solutions
Rohan
Specialist
Specialist

Hi,

Dynamic Labeling in Pivots is not possible it seems, But there is a work around to achieve your desired output, Please check the attached image. I have used a concept called dummy dimensions where I create the labels & their required sort order for the required Pivot table in an unlinked table in the load script. & then use Pick () in the measure to get the expressions in the correct order like for eg :

pick(Dsort,
sum({<Type={"Frais"},Measure={"GH"}>} Value),
sum({<Type={"Frais"},Measure={"GM"}>} Value),
sum({<Type={"Frais"},Measure={"SIP"}>} Value),
sum({<Type={"Gain"},Measure={"GH"}>} Value),
sum({<Type={"Gain"},Measure={"GM"}>} Value),
sum({<Type={"Gain"},Measure={"SIP"}>} Value))

 

My Load Script :

T:
Load * INLINE [
Matricule,Nom,Type,Measure,Value
XXX,ADAM,Frais,GH,1
XXX,ADAM,Frais,GM,1
XXX,ADAM,Frais,SIP,
XXX,ADAM,Gain,GH,12
XXX,ADAM,Gain,GM,43
XXX,ADAM,Gain,SIP,3
HHHH,TAZ,Frais,GH,91
HHHH,TAZ,Frais,GM,3
HHHH,TAZ,Frais,SIP,46
HHHH,TAZ,Gain,GH,77
HHHH,TAZ,Gain,GM,90
HHHH,TAZ,Gain,SIP,0
];

Dummy:  // the unlinked table for the labels, you will have to add the new expressions here in future..
Load
Dim1,
Dim1&' '&Dim2 as Dim2,
Dsort
inline [
Dim1,Dim2,Dsort
Frais,GH,1
Frais,GM,2
Frais,SIP,3
Gain,GH,4
Gain,GM,5
Gain,SIP,6];


Exit Script;

 

 

Regards,

Rohan.

View solution in original post

6 Replies
Rohan
Specialist
Specialist

Hi,

Just Use these as your Measure Name:

1) Type &' GH';

2) Type &' GM';

3) Type &' SIP'

 

Regards,

Rohan.

RanOuerg
Creator
Creator
Author

Hello,

I attempted to concatenate them:

Name = Type & ' GH'

But it doesn't seem to work; I still only see "GH" as the name.

Rohan
Specialist
Specialist

Hi,

Dynamic Labeling in Pivots is not possible it seems, But there is a work around to achieve your desired output, Please check the attached image. I have used a concept called dummy dimensions where I create the labels & their required sort order for the required Pivot table in an unlinked table in the load script. & then use Pick () in the measure to get the expressions in the correct order like for eg :

pick(Dsort,
sum({<Type={"Frais"},Measure={"GH"}>} Value),
sum({<Type={"Frais"},Measure={"GM"}>} Value),
sum({<Type={"Frais"},Measure={"SIP"}>} Value),
sum({<Type={"Gain"},Measure={"GH"}>} Value),
sum({<Type={"Gain"},Measure={"GM"}>} Value),
sum({<Type={"Gain"},Measure={"SIP"}>} Value))

 

My Load Script :

T:
Load * INLINE [
Matricule,Nom,Type,Measure,Value
XXX,ADAM,Frais,GH,1
XXX,ADAM,Frais,GM,1
XXX,ADAM,Frais,SIP,
XXX,ADAM,Gain,GH,12
XXX,ADAM,Gain,GM,43
XXX,ADAM,Gain,SIP,3
HHHH,TAZ,Frais,GH,91
HHHH,TAZ,Frais,GM,3
HHHH,TAZ,Frais,SIP,46
HHHH,TAZ,Gain,GH,77
HHHH,TAZ,Gain,GM,90
HHHH,TAZ,Gain,SIP,0
];

Dummy:  // the unlinked table for the labels, you will have to add the new expressions here in future..
Load
Dim1,
Dim1&' '&Dim2 as Dim2,
Dsort
inline [
Dim1,Dim2,Dsort
Frais,GH,1
Frais,GM,2
Frais,SIP,3
Gain,GH,4
Gain,GM,5
Gain,SIP,6];


Exit Script;

 

 

Regards,

Rohan.

RanOuerg
Creator
Creator
Author

Thank you @Rohan  it seems to work with this solution 

RanOuerg
Creator
Creator
Author

Hello @Rohan 

I have an other case where i shoud group dimensions like this photo and mesures in the same table:

Exemple of giving group for dimensions:

RanMH_0-1713521346870.png

Is it possible with qlik sense??

Thank you in advance.

 

Rohan
Specialist
Specialist

Hi,

Try the following extension : 

https://github.com/ajaykakkar93/TableBox

Credits : @ajaykakkar93 

 

Regards,

Rohan.