Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have requirement where I need to top two dimension names in a text object.
My Data as below:
Table:
LOAD * INLINE [
Custtomer, Column1, Column2, Column3
A, 1, 0, 0
B, 1, 1, 0
C, 0, 0, 1
D, 1, 1, 1
E, 1, 1, 0
F, 0, 0, 1
G, 0, 1, 0
H, 0, 1, 1
I, 1, 0, 1
J, 0, 0, 0
K, 0, 0, 1
L, 0, 1, 0
M, 0, 1, 1
N, 1, 0, 0
O, 1, 0, 1
P, 1, 1, 0
Q, 1, 1, 1
];
O/p:
What I need is,
In above table chart we have top two Combination with count =3
1.Only Column3 have three Customers with combination of
Column1+ Column2+Column3
0,0,1
and
2.Column1+ Column2 we have three Customers with combination of
Column1+ Column2+Column3
1,1,0
In a text Object I need to the top dimension names as below because the count of customer with above combination is more when comapred with the others.
Column3
and
Column1+ Column2
Try this
=Concat(Aggr(
Left(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)) or
$(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net), 2),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + ')),
Len(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)) or
$(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net), 2),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + '))) - 2)
, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net), ' and ')
Try this
=Concat(Aggr(
Left(If(Count(Custtomer) = Max(TOTAL Aggr(Count(Custtomer), Column1, Column2, Column3)), If(Column1 = 1, 'Column 1 + ')&If(Column2 = 1, 'Column 2 + ')&If(Column3 = 1, 'Column 3 + ')),
Len(If(Count(Custtomer) = Max(TOTAL Aggr(Count(Custtomer), Column1, Column2, Column3)), If(Column1 = 1, 'Column 1 + ')&If(Column2 = 1, 'Column 2 + ')&If(Column3 = 1, 'Column 3 + '))) - 2)
, Column1, Column2, Column3), ' and ')
Excellent Sunny,
Thanks for fast resposne.
I'll try and let you know the outcome.
Thanks,
S k
Hi Sunny,
I have used the below Expression in my application
=Concat(Aggr(Left(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + ')),
Len(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + '))) - 2)
, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access), ' and ')
vTest:Count(DISTINCT{<Customer_Flag={'1'},Product_Family={'Expected_Revenue_Mobile_Voice_and_Incoming','Expected_Revenue_Mobile_Broadband','Expected_Revenue_Fixed_Connectivity_Access','Expected_Revenue_M2M_Services','Expected_Revenue_One_Net'},Customer_Flag={'0'},Measure-={0}>}Account_Name)
The text object is displaying only when I do the selection otherwise showing empty.
Is there any issue with my expression.
Thanks
Can you share your app? Just looking at the expression might not really help me find the issue?
I think you might forgot to add the other two dimensions in your Aggr() function
=Concat(Aggr(
Left(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + ')),
Len(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + '))) - 2)
, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net), ' and ')
Hi Sunny,
Thanks
The expression is working fine but I could only the Top 1 Dimension field value is being displayed.
I Need to show the Top 2 dimension field name as well.
Thanks
Try this
=Concat(Aggr(
Left(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)) or
$(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net), 2),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + ')),
Len(If($(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net)) or
$(vTest) = Max(TOTAL Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net), 2),
If(LEAD_Mobile_Voice_and_Incoming = 1, 'LEAD_Mobile_Voice_and_Incoming + ')&If(LEAD_Mobile_Broadband = 1, 'LEAD_Mobile_Broadband + ')&If(LEAD_Fixed_Connectivity_Access = 1, 'LEAD_Fixed_Connectivity_Access + ')&If(LEAD_M2M_Services = 1, 'LEAD_M2M_Services + ')&If(LEAD_One_Net = 1, 'LEAD_One_Net + '))) - 2)
, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net), ' and ')
Hi,
As usual you are a gem and I bow to your knowledge in Qlik.
If you don't mind can you please delete the attachment.
I have used the fileds from my main application.
Thanks
Attachments are removed from my responses