Lost customers

[Pierremeesters]

Hi,

i have a list of customers with invoice history.

for example: 

now i want to count all the distinct customer names that do not have an invoice date with year 2022. but i have no idea how to do this or where to start.

So i want to count customers that didn't order in 2022

[deepanshuSh]

Count (Distinct ({<Year(invoice Date)-={'2022'}>}CustomerName)) Or another way to do it is using p & e in set analysis. 

[Diego_780]

Hi,

In this situation, you want to use Set Expressions. I think what you want is this expression:

Count({<[Invoice date]= {"<01/01/2022"}>} distinct Customer_Name)

Or whatever your dimensions are called.

[Pierremeesters]

If i use this Expression it will count the Invoice date lower then 01/01/2022 aswell right?

For example : 

An customers has bought in 2020 - 2021 and in 2022. now it counts this customer as 1 right? Because it has an invoice date lower then 01/01/2022. i want it that IF the customer has No orders later then 2022 it needs to count 1. if it has an invoice in 2020 and one in 2022 i don't need to count it.

[Diego_780]

Mmm, yeah, I get all distinct Customer Name that have invoice data below 1/1/22. I guess you should use some If statements to get around, maybe smth like this?

If(Count({<[Invoice date]= {"<01/01/2022"}>} Customer_Name) > 1 and Count({<[Invoice date]= {">=01/01/2022"}>} Customer_Name) = 0, Count({<[Invoice date]= {"<01/01/2022"}>} distinct Customer_Name), 'Don´t count! It has records in 2022!!')

[Pierremeesters]

Hi, 

It kinda does what i need but not completely, when i use it like this: 

"

If(Count({<[Invoice date]= {"<01/01/2022"}>} Name)  and Count({<[Invoice date]= {">=01/01/2022"}>} Name) = 0, Count({<[Invoice date]= {"<01/01/2022"}>} distinct Name), 'Don´t count! It has records in 2022!!')

"

If i select a specific customer that doesn't have an invoice later then 1/1/2022 now it counts 1. When i select a customer with invoices in 2022 it doesn't count as 1.. so this works. BUT when i do not specifically select an customer it doesn't count anything. :

Without selection:

With Customer that has no invoice later then 1/1/2022 selected : 

With Customer that has invoice's later then 1/1/2022 selected :