Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
raju_insights
Partner - Creator III
Partner - Creator III

How to apply logical operators for the same field in set analysis?

Hi Techies,

For the table,

CUSTOMER_ACTIVITY:

LOAD * INLINE [

   Num, Month, Customer, Bundle

    1,Jan, A, Data

    1,Jan, B, Data

    1,Jan, B, Voice

    1,Jan, A, Voice

    2,Feb, A, Data

    2,Feb, B, Voice

    2,Feb, C, Data

    2,Feb, B, Data

    3,Mar, A, Data

    3,Mar, B, Voice

    3,Mar, C, Data

    3,Mar, C, Voice

];

Using set analysis:

1. Count of distinct customers who used only Data Bundle in each month.

2. Count of distinct customers who used only Voice Bundle in each month.

3. Count of distinct customers who used Both Data and Voice in each month.

Here I have attached a sample document.

1 Solution

Accepted Solutions
effinty2112
Master
Master

Hi Rangaraju,

Try:

1. Count of distinct customers who used only Data Bundle in each month.

Count(Distinct Customer) - Count({$<Bundle -= {'Data'}>}Distinct Customer)

2. Count of distinct customers who used only Voice Bundle in each month.

Count(Distinct Customer) - Count({$<Bundle -= {'Voice'}>}Distinct Customer)

3. Count of distinct customers who used Both Data and Voice in each month.

sum(aggr(if(Concat(DISTINCT Bundle,'|',Bundle)='Data|Voice',1),Customer,Month))

giving:

Month Only Data Only Voice Both
Jan002
Feb201
Mar11

1

regards

Andrew

View solution in original post

12 Replies
its_anandrjs

You can try this also

1. For Data Only =Count(Distinct {<Bundle={'Data'}>}  Customer)

2. For Voice Only =Count(Distinct {<Bundle={'Voice'}>}  Customer)

3. For Both =Count(Distinct {<Bundle={'Data','Voice'}>}  Customer)


See attached also

raju_insights
Partner - Creator III
Partner - Creator III
Author

Hi Anand Chouhan,

1. Count of distinct customers who used only Data Bundle in each month.

For example: in the month of Jan, no customer used only Data. So it should be 0. But your expression gives 2.


2. Same as above.

3. Count of distinct customers who used Both Data and Voice in each month.

For example: in the month of Feb, B used Both Data and Voice. So it is 1. But your expression gives 3.

Miguel_Angel_Baeyens

There will be more ways, one I have used is

  1. Data only: Count({< Customer = E({< Bundle = {'Voice'} >}) >} DISTINCT Customer)
  2. Voice only: Count({< Customer = E({< Bundle = {'Data'} >}) >} DISTINCT Customer)
  3. Both: Count({< Bundle = E({< Customer = {"=Count(DISTINCT Bundle) = 1"} >}) >} DISTINCT Customer)

If the data set is not big, If() will also work as good.

raju_insights
Partner - Creator III
Partner - Creator III
Author

Hi Miguel,

This is not working,

1. everything becomes 0.

2. everything becomes 0.

3. It is just giving the Count of Distinct Customers.

bharani8
Creator III
Creator III

I think u will have to use aggr() to derive this..

raju_insights
Partner - Creator III
Partner - Creator III
Author

Hi Bharani,

But how it can give only Data, only voice and both voice and data customers.

effinty2112
Master
Master

Hi Rangaraju,

Try:

1. Count of distinct customers who used only Data Bundle in each month.

Count(Distinct Customer) - Count({$<Bundle -= {'Data'}>}Distinct Customer)

2. Count of distinct customers who used only Voice Bundle in each month.

Count(Distinct Customer) - Count({$<Bundle -= {'Voice'}>}Distinct Customer)

3. Count of distinct customers who used Both Data and Voice in each month.

sum(aggr(if(Concat(DISTINCT Bundle,'|',Bundle)='Data|Voice',1),Customer,Month))

giving:

Month Only Data Only Voice Both
Jan002
Feb201
Mar11

1

regards

Andrew

Miguel_Angel_Baeyens

That correct, I had selected a month:

  1. Count(If(Aggr(Count(DISTINCT Bundle), Customer, Month) = 1 AND Bundle = 'Data', Customer))
  2. Count(If(Aggr(Count(DISTINCT Bundle), Customer, Month) = 1 AND Bundle = 'Voice', Customer))
  3. Count(If(Aggr(Count(DISTINCT Bundle), Customer, Month) > 1, Customer))

I may have missed some parentheses

raju_insights
Partner - Creator III
Partner - Creator III
Author

Hi Andrew,

Thanks for the solution. I have a doubt. What is the difference between "Concat(Distinct Bundle,'|',Bundle)" and "Concat(Distinct Bundle,'|')"