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: 
kicchu465
Creator
Creator

Displaying Dimension Names in Text Object

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

1 Solution

Accepted Solutions
sunny_talwar

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 ')

View solution in original post

20 Replies
sunny_talwar

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 ')

Capture.PNG

kicchu465
Creator
Creator
Author

Excellent Sunny,

Thanks for fast resposne.

I'll try and let you know the outcome.

Thanks,

S k

kicchu465
Creator
Creator
Author

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

sunny_talwar

Can you share your app? Just looking at the expression might not really help me find the issue?

sunny_talwar

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 ')

kicchu465
Creator
Creator
Author

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

sunny_talwar

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 ')

kicchu465
Creator
Creator
Author

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

sunny_talwar

Attachments are removed from my responses