topic Re: count customers with set analysis in QlikView

count customers with set analysis

felcar2013 — Mon, 23 Mar 2015 11:07:52 GMT

i want to Count the number of customers who bought through only one purchase channel for a selected period of time. if during that period of time a customer bought in 2 or 3 different channels, then the customer is disregarded from the Count.

i used the formula

count({$<PurchaseChannel= {1}>} AccountNr) - count({$<PurchaseChannel= {2,3}>} AccountNr) or

num(count({$<PurchaseChannel={1}-{2,3}>}DISTINCT AccountNr),'#.##0')

but both of them are not working

I attached a sample file with the following table:

trans:
load * inline [
AccountNr,PurchaseChannel,Date
1,1,02.02.2015
1,2,02.02.2015
1,2,03.02.2015
2,1,03.02.2015
2,3,03.02.2015
3,2,03.02.2015
4,1,03.02.2015
4,1,04.02.2015
4,2,04.02.2015
5,1,04.02.2015
5,1,05.02.2015
];

From the example, i should get for Purchase Channel 1 only one customer (which is the number 5), if the selected period is from [02.02.2015 to 05.02.2015]

any idea to solve this? is this possible?

thanks

Re: count customers with set analysis

sagarkharpude — Mon, 23 Mar 2015 11:12:47 GMT

Please try like this

=count({$<PurchaseChannel= {'*'}-{2,3}>} AccountNr)

Re: count customers with set analysis

felcar2013 — Mon, 23 Mar 2015 11:15:21 GMT

it did not work

i Need to specify 1 as Purchase Channel

Re: count customers with set analysis

Mon, 23 Mar 2015 11:22:01 GMT

Hi ,

Check this its for selected date...

='Range select count '&(count( {<Date ={">=$(#mindate) <=$(#maxdate)"} ,PurchaseChannel={1}>}AccountNr ))

Regards

Vimlesh

Re: count customers with set analysis

felcar2013 — Mon, 23 Mar 2015 11:50:07 GMT

hi, thanks

the selected date is fine, there is no much Change to do there, what the difficulty is, is that i only Need to Count the customers, who for a selected period of time, ONLY used one channel.

For ex. for the period of time 02.02.2015 to 05.02.1025, customer number 1 used on 02.02 purchase channels 1 and 2 and on 03.02 he used purchase channel nr. 2 again. this means, i will not consider Customer Nr. 1 into the Count, because he used purchase channels 1 and 2 for the selected period o time.

In case of customer nr. 5, he used purchase channel 1 on the 04.02 and the same channel on the 05.02, so i will consider this customers in the total Count, because for the selected period of time he used only one purchase channel.

The period of time must be selected from the list box.

Re: count customers with set analysis

maxgro — Mon, 23 Mar 2015 21:55:13 GMT

number of customers who bought through only one purchase channel for a selected period

sum(aggr(count({$ <AccountNr={"=count(distinct PurchaseChannel)=1"}>} DISTINCT AccountNr), AccountNr))

Re: count customers with set analysis

felcar2013 — Wed, 25 Mar 2015 13:09:05 GMT

thanks, i Need to exclude from the generated set, the Customers (AccountNr) who, for the same set and selected Dates, bought in PurchaseChannel 2 or 3 or in both

how can i put the "exclusion" condition, so that i exclude the Account Nr. who bought besides channel 1 on channel 2 and 3 for the same period of time?

Re: count customers with set analysis

maxgro — Wed, 25 Mar 2015 18:57:32 GMT

try with dimension PurchaseChannel

and expression

concat({$ <AccountNr={"=count(distinct PurchaseChannel)=1"}>} DISTINCT AccountNr, ',')

Re: count customers with set analysis

felcar2013 — Thu, 26 Mar 2015 09:08:52 GMT

hi, thanks for your help

the original table contains more than 2 Million distinct customers, so that will be difficult. generally i have to get the number of customers for exclusively only one purchase channel. A customer may have bought via purchase channel 1 on the 02.02.2015 but on the 03.02.2015 the same customer bought again, but via purchase channel 2. This means, i cannot Count this customer, since he bought in the selected period of time in two different channels.

from the test data, only customer 5 fullfills the restriction of having bought only via purchase channel 1 for the period of time 02.02.2015 to 05.02.2015 (this period of time shall be selected variably in the list box)

1. select purchase channel: either 1, 2 or 3

2. get list of selected customers who complain with the restriction (say Purchase Channel 1)

3. Compare the customers from list in 2. with the full list of customers and check if they also bought in a purchase channel different than the one selected in 1.

4. Count the customers who bought only in the purchase channel defined in 1

Re: count customers with set analysis

felcar2013 — Thu, 02 Apr 2015 08:33:26 GMT

hi, i transformed columns into rows, then applied your formula, with the condition that purchasechannel shall be either 1,2 or 3 and it worked

thanks

Felipe

Original Table:

trans:
load * inline [
AccountNr,PurchaseChannel,Date
1,1,02.02.2015
1,2,02.02.2015
1,2,03.02.2015
........

];

Transformed: where PP = PurchaseChannel (1,2,3 converted to 1 or 0s)

AccountNr,Date,PC1,PC2,PC3
1,02.02.2015,1,1,0
..............