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

[raju_insights]

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.

[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]

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]

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]

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

[raju_insights]

Hi Bharani,

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

[effinty2112 · Accepted solution]

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
Jan	0	0	2
Feb	2	0	1
Mar	1	1	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]

Hi Andrew,

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