Lapsed Customers Calculation

monikar86 — Wed, 25 Nov 2020 16:16:04 GMT

We would like to count total customers each month who had shopped at some point in the previous years (they have placed at least one order on the website) but then they have not shopped in the 12 months prior to the reporting month. Each month we want to show what this count of customers had been at that point in time, but the issue we encounter is that if one of these customers returns to shop now or anytime during the period we are reporting on, they will be taken out of the historical count of 12 months inactive customers in the month reported previously. As an example, please see below:

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2015

YES

No1

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2016

No2

No3

No4

YES

No1

No2

No3

No4

No5

No6

No7

No8

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2017

No9

No10

No11

No12

No13

YES

No1

No2

No3

No4

No5

No6

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2018

No7

No8

No9

No10

No11

No12

No13

No14

YES

No1

No2

No3

A customer purchased in Apr 2016.
They had 13 months of No purchases.
In May 2017, this customer has to be counted in the total count of 12 months inactive customers for May 2017
However, because we are not looking at this in May 2017 but today and trying to pull historical data, the customer has purchased again in Jun 2017 and Apr-16 is no longer their last order date. They are active again and we can’t see that they had lapsed previously. This behaviour reduces the total count of 12 month inactive customers each month and is preventing us from understanding behaviours relating to customers re-engaging with our brand.

I've attached an example how we calculate the numbers.

We've tried below formula. but this works for today's date not for the historical data.

=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-26)))"}>} distinct [EMSCustomerNo])

=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-25)))"}>} distinct [EMSCustomerNo])

=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-24)))"}>} distinct [EMSCustomerNo])

=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-23)))"}>} distinct [EMSCustomerNo])

count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-22)))"}>} distinct [EMSCustomerNo])

=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-21)))"}>} distinct [EMSCustomerNo])

=count ({< [MaxOrderDate]={"<=$(=MonthEnd(AddMonths(EndDate,-14)))"}>} distinct [EMSCustomerNo])

EndDate =date(floor(monthend(Today())), 'DD/MM/YYYY')

Attached the table we want to create.

Do you have any suggestions how we can solve this problem?

topic Lapsed Customers Calculation in QlikView

Lapsed Customers Calculation