Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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.