# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Contributor II

## "Recovered customers"

Hello everybody,

I have a question regarding the following situation.

The data are as follows:

 Customer InvoiceDate SALES 00123 01.07.2012 200 € 97262 15.06.2013 300 € 45677 17.08.2017 50 € 23142 18.09.2019 100 € 42531 21.01.2021 150 € ...

Now we'd like to count the "recovered customers" (KPI). A recovered customer has to fulfill three conditions:

1) Sales before 31.12.2017

2) No Sales between 01.01.2018 and 31.12.2020

3) Sales after 01.01.2021

What formula would you recommend?

Thank you very much in advance!

1 Solution

Accepted Solutions

May be like this :

=count({<Customer={"=sum({<InvoiceDate={[<31.12.2017]}>} Sales)>0"}*{"=sum({<InvoiceDate={[>01.01.2018 <01.12.2020]}>} Sales)>0"}*{"=sum({<InvoiceDate={[>01.01.2021]}>} Sales)>0"}>} distinct Customer)

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
6 Replies

@ToJu  try this :

=sum({<InvoiceDate={"<31.12.2017"}>} SALES)

=count({<InvoiceDate={">01.01.2018 <01.12.2020"}>} Customer)

=sum({<InvoiceDate={">01.01.2021"}>} SALES)

output

:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor II
Author

@Taoufiq_Zarra Thank you very much!

But I need to count the customers, if all three conditions are true.

Is it possible to combine these formulas?

@ToJu  can you share the expected output from this sample ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor II
Author

Let’s have a look at the following table:

 Customer InvoiceDate Sales 123 01.06.2012 100 € 123 01.10.2019 200 € 123 01.03.2021 300 € 456 01.09.2013 50 € 456 01.04.2021 250 €

The expected output from this example should be "1" (--> 456), because:

- Customer 456 fulfills every condition

- Customer 123 doesn't fulfill condition 2 (there are sales between 01/2018 and 12/2020)

May be like this :

=count({<Customer={"=sum({<InvoiceDate={[<31.12.2017]}>} Sales)>0"}*{"=sum({<InvoiceDate={[>01.01.2018 <01.12.2020]}>} Sales)>0"}*{"=sum({<InvoiceDate={[>01.01.2021]}>} Sales)>0"}>} distinct Customer)

output:

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor II
Author

That's it. Thank you very much! 🙂

Tags
Community Browser