# Evaluating dates by customer

Hi All,

I have a table in QS with customerId and two types of date.

I’d like to evaluate each customer and dates with a field called DATE following these rules:

CONDITIONS BY ORDER
1 DATE >= CUSTOMER DATE
2 REGISTER DATE <= DATE
3 MAX(REGISTER DATE) WHERE <= DATE

DATE ene/08/2023

CUSTOMERID REGISTER DATE CUSTOMER DATE
A001 ene/01/2023 ene/01/2023
A001 ene/02/2023 ene/01/2023
A001 ene/03/2023 ene/01/2023
A002 ene/04/2023 ene/02/2023
A002 ene/07/2023 ene/02/2023
A002 ene/15/2023 ene/02/2023
A003 ene/07/2023 ene/06/2023
A003 ene/09/2023 ene/06/2023
A003 ene/11/2023 ene/06/2023
A003 ene/14/2023 ene/06/2023
A004 ene/11/2023 ene/10/2023
A004 ene/12/2023 ene/10/2023
A004 ene/13/2023 ene/10/2023
A004 ene/14/2023 ene/10/2023

In the end I’d like to see the table with this new calculated field:

CUSTOMERID REGISTER DATE CUSTOMER DATE CALCULATED FIELD
A001 ene/01/2023 ene/01/2023 0
A001 ene/02/2023 ene/01/2023 0
A001 ene/03/2023 ene/01/2023 1
A002 ene/04/2023 ene/02/2023 0
A002 ene/07/2023 ene/02/2023 1
A002 ene/15/2023 ene/02/2023 0
A003 ene/07/2023 ene/06/2023 1
A003 ene/09/2023 ene/06/2023 0
A003 ene/11/2023 ene/06/2023 0
A003 ene/14/2023 ene/06/2023 0
A004 ene/11/2023 ene/10/2023 0
A004 ene/12/2023 ene/10/2023 0
A004 ene/13/2023 ene/10/2023 0
A004 ene/14/2023 ene/10/2023 0

I can create the two first conditions but I cannot create the third condition
|3|MAX(REGISTER DATE) WHERE <= DATE|

I’m trying to use MAX and MAXIF but I’m receiving errors.

Dear @KLIKIN

I think this can be resolved by leveraging calculated fields and lac-w functions. Please try the below, though first i have a question. Where does the ‘date’ come from? is it another field in the table or a parameter? In my test i used a parameter for DATE. Screenshots below.

1. Create calculated fields for 'register data 'and ‘customer date’
2. Create calculated field for ‘max register date by customer’
3. Create calculated field for condition.