Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
tmumaw
Valued Contributor

Current, New and Lost Customers

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

Tags (1)
1 Solution

Accepted Solutions
Highlighted

Re: Current, New and Lost Customers

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;

View solution in original post

10 Replies
Highlighted

Re: Current, New and Lost Customers

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

Highlighted
tmumaw
Valued Contributor

Re: Current, New and Lost Customers

Thanks.  I will give it a try.

Highlighted

Re: Current, New and Lost Customers

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

Highlighted
tmumaw
Valued Contributor

Re: Current, New and Lost Customers

But it could be an old customer

Highlighted

Re: Current, New and Lost Customers

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?

Highlighted
tmumaw
Valued Contributor

Re: Current, New and Lost Customers

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

Highlighted

Re: Current, New and Lost Customers

I was just looking for 10 rows may be

Highlighted

Re: Current, New and Lost Customers

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;

View solution in original post

Highlighted

Re: Current, New and Lost Customers

Did you ever get to check this?