Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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.

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Master
Master

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

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
Highlighted

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

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

Highlighted
Partner
Partner

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

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.

Highlighted

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

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.

Highlighted
Partner
Partner

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

Hi Miguel,

This is not working,

1. everything becomes 0.

2. everything becomes 0.

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

Highlighted
Creator III
Creator III

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

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

Highlighted
Partner
Partner

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

Hi Bharani,

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

Highlighted
Master
Master

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

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

Highlighted

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

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

Highlighted
Partner
Partner

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

Hi Andrew,

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