# New to QlikView

Discussion board where members can get started with QlikView.

New Contributor

## Lapsed Customers Calculation

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 No No No No No No No No No No 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?

Tags (1)