Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

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

Highlighted
Creator
Creator

Excellent Sunny,

Thanks for fast resposne.

I'll try and let you know the outcome.

Thanks,

S k

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted

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

Highlighted
Creator
Creator

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

Highlighted

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

Highlighted
Creator
Creator

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

Highlighted

Attachments are removed from my responses