topic Displaying Dimension Names in Text Object in QlikView

Displaying Dimension Names in Text Object

kicchu465 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Displaying Dimension Names in Text Object

sunny_talwar — Mon, 04 Jun 2018 12:38:43 GMT

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

Re: Displaying Dimension Names in Text Object

kicchu465 — Mon, 04 Jun 2018 13:06:29 GMT

Excellent Sunny,

Thanks for fast resposne.

I'll try and let you know the outcome.

Thanks,

S k

Re: Displaying Dimension Names in Text Object

kicchu465 — Mon, 04 Jun 2018 13:41:28 GMT

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

Re: Displaying Dimension Names in Text Object

sunny_talwar — Mon, 04 Jun 2018 13:43:57 GMT

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

Re: Displaying Dimension Names in Text Object

sunny_talwar — Mon, 04 Jun 2018 15:47:13 GMT

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

, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net), ' and ')

Re: Displaying Dimension Names in Text Object

kicchu465 — Mon, 04 Jun 2018 15:57:53 GMT

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

Re: Displaying Dimension Names in Text Object

sunny_talwar — Mon, 04 Jun 2018 16:03:32 GMT

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

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

, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net), ' and ')

Re: Displaying Dimension Names in Text Object

kicchu465 — Mon, 04 Jun 2018 16:13:41 GMT

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

Re: Displaying Dimension Names in Text Object

sunny_talwar — Mon, 04 Jun 2018 16:15:56 GMT

Attachments are removed from my responses

Re: Displaying Dimension Names in Text Object

kicchu465 — Tue, 05 Jun 2018 07:53:43 GMT

Hi Sunny,

I need a bit of help from you.

If possible could you please explain how the above expression works.

Thanks

Re: Displaying Dimension Names in Text Object

sunny_talwar — Tue, 05 Jun 2018 11:03:48 GMT

Is there a specific part of the expression that you don't understand?

Re: Displaying Dimension Names in Text Object

kicchu465 — Tue, 05 Jun 2018 17:38:24 GMT

Hi Sunny,

Basically I am new to qlikview and I am trying to understand the expression.

If you can explain me the else part it would be helpful.

Thanks

Re: Displaying Dimension Names in Text Object

sunny_talwar — Tue, 05 Jun 2018 18:02:48 GMT

I tried to look for the else part in my expression... but I don't think I see any... where exactly do you see the else part of the if statement?

Re: Displaying Dimension Names in Text Object

kicchu465 — Wed, 06 Jun 2018 06:49:06 GMT

Hi Sunny,

There is change in my requirment by the user.

Earlier they were asking the top two values but they wanted to show the top only if there is combination.

For example in the attached application we have table as below.

The top 2 will be Combination of

1.LEAD_Mobile_Broadband+LEAD_Mobile_Voice_and_Incoming

2.LEAD_Mobile_Voice_and_Incoming

The User is now asking that they dont want to see the if there is single product. They wanted to see only when there is combination of products. Basically the User want to see only the top two combination of Products.

i.e the next top value with the combination is

LEAD_Mobile_Broadband+LEAD_Mobile_Voice_and_Incoming +

LEAD_Fixed_Connectivity_Access

The Output should be

Thanks

S k

Re: Displaying Dimension Names in Text Object

sunny_talwar — Wed, 06 Jun 2018 09:05:48 GMT

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

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

, LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net),' + ' & Chr(10), -Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net))

Re: Displaying Dimension Names in Text Object

kicchu465 — Wed, 06 Jun 2018 09:29:27 GMT

Hi Sunny,

The Top 2 bundle should be

1.LEAD_Mobile_Broadband+LEAD_Mobile_Voice_and_Incoming

2.LEAD_Mobile_Broadband+LEAD_Mobile_Voice_and_Incoming +

LEAD_Fixed_Connectivity_Access

We should be igroning if any there is only sigle product in the top 2 and show only the Combination.

Re: Displaying Dimension Names in Text Object

sunny_talwar — Wed, 06 Jun 2018 11:20:06 GMT

Is there a field in your database which gives a name for the combination of services installed by users? So, for example when the user take the first row... he is enrolled in XYZ service?

It can be done without that unique field, but your expression will be extremely complex. If there is no such field... you can create one like this in the script which can also be very helpful....

RangeSum(LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net) as LEAD_Check,

But let me know if script changes are not allowed and I can work on a non-script based solution

Re: Displaying Dimension Names in Text Object

kicchu465 — Wed, 06 Jun 2018 17:35:47 GMT

Hi Sunny,

If there is a solution which can achieved at script Level then there is no Issue,We can do the script level changes

Thanks

Re: Displaying Dimension Names in Text Object

sunny_talwar — Wed, 06 Jun 2018 17:37:08 GMT

Can you rerun with this code added and repost the app here

RangeSum(LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access, LEAD_M2M_Services, LEAD_One_Net) as LEAD_Check,