topic Re: Current, New and Lost Customers in QlikView

Current, New and Lost Customers

tmumaw — Mon, 30 Oct 2017 11:05:50 GMT

Good morning,

I need a little help finding current, new and lost customers. The first part of the script finding old customer and new customers seems to work, but the lost customers I am having a hard time with. Any ideas?

Thanks

LEFT JOIN (VBRK_R)
LOAD [Billing Date],
FISYR
RESIDENT FiscalCalendar;

LEFT JOIN (VBRP_R)
LOAD %BillingDocument_Key,
[Billing Date],
FISYR
RESIDENT VBRK_R;

Old_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
[Sold To] AS [Old Sold To],
1 AS Old_Cust
RESIDENT VBRP_R
WHERE FISYR >= '$(vPrevYear)' and
FISYR < '$(vFisYr)'
;

New_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
1 AS New_Cust
RESIDENT VBRP_R
WHERE FISYR = '$(vFisYr)'
AND NOT EXISTS([Old Sold To], [Sold To])
;

Lost_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
1 AS Lost_Cust
RESIDENT VBRP_R
WHERE FISYR <= '$(vLastServiceYear)'
AND NOT EXISTS ([Old Sold To], [Sold To])
;

Re: Current, New and Lost Customers

sunny_talwar — Mon, 30 Oct 2017 11:20:06 GMT

May be this

Lost_Customers:
LEFT JOIN (KNA1_NEW)
LOAD DISTINCT [Sold To],
1 AS Lost_Cust
RESIDENT VBRP_R
WHERE FISYR <= '$(vLastServiceYear)';

What if you do the lost without the Where statement

Re: Current, New and Lost Customers

tmumaw — Mon, 30 Oct 2017 11:22:07 GMT

Thanks. I will give it a try.

Re: Current, New and Lost Customers

sunny_talwar — Mon, 30 Oct 2017 11:24:05 GMT

My bad, that isn't going to work... thinking about it now...

Re: Current, New and Lost Customers

tmumaw — Mon, 30 Oct 2017 11:25:16 GMT

But it could be an old customer

Re: Current, New and Lost Customers

sunny_talwar — Mon, 30 Oct 2017 11:27:23 GMT

Would you be able to share few rows of data to test this out? I am difficulty imagining this with the additional complexity of the variables you have which I don't really know expand to?

Re: Current, New and Lost Customers

tmumaw — Mon, 30 Oct 2017 11:35:20 GMT

Way too much data to have to share to get the results.

Re: Current, New and Lost Customers

sunny_talwar — Mon, 30 Oct 2017 11:36:06 GMT

I was just looking for 10 rows may be

Re: Current, New and Lost Customers

sunny_talwar — Tue, 31 Oct 2017 01:16:59 GMT

Try this

Table:

LOAD * INLINE [

Customer, Year

A, 2016

B, 2016

C, 2016

B, 2017

C, 2017

D, 2017

];

Old_Customer:

LOAD DISTINCT Customer,

Customer as Old_Customer,

1 as Old_Cust

Resident Table

Where Year <= 2016;

New_Customer:

LOAD DISTINCT Customer,

Customer as New_Customer,

1 as New_Cust

Resident Table

Where not Exists(Old_Customer, Customer) and Year = 2017;

Temp:

LOAD DISTINCT Customer as Temp_Customer,

1 as New_Cust

Resident Table

Where Exists(Old_Customer, Customer) and Year = 2017;

Lost_Customer:

LOAD Distinct Customer,

1 as Lost_Cust

Resident Table

Where not Exists(Temp_Customer, Customer) and Year <= 2016;

DROP Table Temp;

Re: Current, New and Lost Customers

sunny_talwar — Wed, 08 Nov 2017 14:32:49 GMT

Did you ever get to check this?

Re: Current, New and Lost Customers

tmumaw — Wed, 08 Nov 2017 15:08:11 GMT

Sorry Sunny I got pulled to another project. I will be testing this out next week. Sorry for the delay. Thanks for all your help.