Current, New and Lost Customers

[tmumaw]

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])
;

[sunny_talwar]

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

[tmumaw]

Thanks.  I will give it a try.

[sunny_talwar]

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

[tmumaw]

But it could be an old customer

[sunny_talwar]

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?

[tmumaw]

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

[sunny_talwar]

I was just looking for 10 rows may be

[sunny_talwar · Accepted solution]

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;

[sunny_talwar]

Did you ever get to check this?