topic Calculate New & Lost Customers in QlikView

Calculate New & Lost Customers

Anonymous — Mon, 23 Jul 2018 01:08:39 GMT

Hi,

I have a query where I need to find out new and lost customers monthly.

New - A customer which did not have a record in last month sales data.

Lost - A customer which had a record in last month's sales data but doesn't have one in this month.

I have found a few solutions on forum here, however, they are not giving me a monthly list, I need to select a month then the numbers come up for only those months. I am looking for solutions that I can work in expressions in the chart and not by making changes in the script.

A sample of my data is as below:

PRODUCTID	SALEID	CUSID	EXTRACTDATE
11	101	A	30/06/2018
12	102	B	30/06/2018
13	103	C	30/06/2018
14	104	D	30/06/2018
15	105	E	30/06/2018
16	106	F	30/06/2018
17	107	G	30/06/2018
18	108	H	30/06/2018
19	109	A	31/05/2018
20	110	C	31/05/2018
21	111	D	31/05/2018
22	112	F	31/05/2018
23	113	G	31/05/2018
24	114	I	31/05/2018
25	115	J	31/05/2018
26	116	K	31/05/2018
27	117	L	31/05/2018
28	118	C	30/04/2018
29	119	F	30/04/2018
30	120	I	30/04/2018
31	121	M	30/04/2018
32	122	N	30/04/2018
33	123	O	30/04/2018
34	124	P	30/04/2018

I want an output like:

ExtractDate	New Customers	Lost Customers
30/04/2018	0	4
31/05/2018	6	4
30/06/2018	3	4

To explain further, the CUSID that we can't find in April becomes New Customers for May and so on. And the CUSID we can't find in May but are there in April becomes lost of April.

Please advise how can this be done within expression.

Thank You in Advance.

Regards,

Roopali

Re: Calculate New & Lost Customers

sunny_talwar — Mon, 23 Jul 2018 11:21:35 GMT

Why do you have 4 lost customers for Apr? Based on the data provided... we don't even know who was and who wasn't present in March (not available).... and similarly why do you have 0 for new customer's in Apr... Since March isn't available... I would assume that all the 7 customers in Apr are all new? Please let us know the logic behind these two things to help you better...

Best,

Sunny

Re: Calculate New & Lost Customers

Anonymous — Tue, 24 Jul 2018 02:36:12 GMT

Hi,

Yes you are right.

I was just trying to show a sample, i should have mentioned 7 New for April and not 0. That's my fault.

And for Lost Customers, i mentioned 4 because these are the customers that are lost from April to May. From the perspective of Extract Date, I wasn't sure if the lost ones will show up in May or April. I figures since the records are on date of April, they may not show up in May and I mentioned in April.

I hope I am clear.

However, if you have a solution where I can show these 4 customers as lost customers in May, that will be great.

Thanks,

Roopali