"Recovered customers"

[ToJu]

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!

[Taoufiq_Zarra]

@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

:

[ToJu]

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

[Taoufiq_Zarra]

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

[ToJu]

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)

[Taoufiq_Zarra · Accepted solution]

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:

[ToJu]

That's it. Thank you very much! 🙂