Displaying Dimension Names in Text Object

[kicchu465]

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

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

[kicchu465]

Excellent Sunny,

Thanks for fast resposne.

I'll try and let you know the outcome.

Thanks,

S k

[kicchu465]

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]

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 · Accepted solution]

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]

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