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