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
Hi Sunny,
I need a bit of help from you.
If possible could you please explain how the above expression works.
Thanks
Is there a specific part of the expression that you don't understand?
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
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?
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
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),' + ' & Chr(10), -Aggr($(vTest), LEAD_Mobile_Voice_and_Incoming, LEAD_Mobile_Broadband, LEAD_Fixed_Connectivity_Access,LEAD_M2M_Services,LEAD_One_Net))
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.
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
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
Sk
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,