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

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

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

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.

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

its_anandrjs — Wed, 30 Aug 2017 08:58:32 GMT

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

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

rajuamet — Wed, 30 Aug 2017 09:04:06 GMT

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.

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

Miguel_Angel_Baeyens — Wed, 30 Aug 2017 09:20:09 GMT

There will be more ways, one I have used is

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

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

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

rajuamet — Wed, 30 Aug 2017 09:27:30 GMT

Hi Miguel,

This is not working,

1. everything becomes 0.

2. everything becomes 0.

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

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

bharani8 — Wed, 30 Aug 2017 09:29:12 GMT

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

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

rajuamet — Wed, 30 Aug 2017 09:32:12 GMT

Hi Bharani,

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

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

effinty2112 — Wed, 30 Aug 2017 09:36:14 GMT

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

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

Miguel_Angel_Baeyens — Wed, 30 Aug 2017 09:37:12 GMT

That correct, I had selected a month:

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

I may have missed some parentheses

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

rajuamet — Wed, 30 Aug 2017 12:57:40 GMT

Hi Andrew,

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

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

effinty2112 — Wed, 30 Aug 2017 13:02:26 GMT

Hi Rangaraju,

The second appearance of Bundle in the expression:

Concat(Distinct Bundle,'|',Bundle)

is as a sort parameter so that when the string concatenation is done we won't get 'Voice|Data' when our if statement is testing for 'Data|Voice' .

cheers

Andrew

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

rajuamet — Wed, 30 Aug 2017 13:40:26 GMT

Hi Andrew,

How can I reverse the sorting? like 'Voice|Data'.

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

rajuamet — Wed, 30 Aug 2017 13:41:17 GMT

Got it thanks.