Evaluating dates by customer

KLIKIN · February 24, 2023, 11:54am

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.

Could you help me please?

Ramon_Lopez · February 24, 2023, 10:37pm

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.

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

Please let me know if this worked in your use case.

thanks!
Ramon Lopez