Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello ,
I have a pivot table with:
Dimension: Matricule , Nom
Column: Type( Frais or Gain)
Mesures : GH,GM,SIP
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"
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.
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.
Hi,
Just Use these as your Measure Name:
1) Type &' GH';
2) Type &' GM';
3) Type &' SIP'
Regards,
Rohan.
Hello,
I attempted to concatenate them:
Name = Type & ' GH'
But it doesn't seem to work; I still only see "GH" as the name.
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.
Thank you @Rohan it seems to work with this solution
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:
Is it possible with qlik sense??
Thank you in advance.
Hi,
Try the following extension :
https://github.com/ajaykakkar93/TableBox
Credits : @ajaykakkar93
Regards,
Rohan.