Expression for finding a transaction closets to a date

[peterstalberg]

Hi experts,

I have a table with ORDER_ID. For each ORDER_ID there are several TRANSACTION_DATE & TRANSACTION_AMOUNT (see below truncated table as an example).

I have problem with finding a set expression returning the TRANSACTION_AMOUNT for the TRANSACTION_DATE closest on or before (<=) the ORDER_DATE+180 days.

I tried this expression but the result is not as expected, works only for one ORDER_ID at a time 😞 

sum(aggr(FirstSortedValue( {$<TRANSACTION_DATE={"<=$(=date(ORDER_DATE+180))"}>}TRANSACTION_AMOUNT, -TRANSACTION_DATE), ORDER_ID))

*************UPDATE***************

The following set expression solved the problem;

sum(aggr(FirstSortedValue({<[TRANSACTION_DATE]= {"=([TRANSACTION_DATE]-[ORDER_DATE])<=180"}>} [TRANSACTION_AMOUNT], -[TRANSACTION_DATE]),ORDER_ID))

*************************************

 Input

ORDER_ID	ORDER_DATE	TRANSACTION_DATE	TRANSACTION_AMOUNT
106104	2021-10-10	2021-10-11	15 000
106104	2021-10-10	2021-10-25	500 000
106104	2021-10-10	2021-11-03	600 000
106104	2021-10-10	2022-05-13	800 000
106104	2021-10-10	2022-09-01	860 000
106104	2021-10-10	2022-09-05	859 945
106104	2021-10-10	2022-09-05	859 945
106104	2021-10-10	2023-01-16	859 945
106104	2021-10-10	2023-01-16	864 299
106104	2021-10-10	2023-01-16	864 299
104579	2021-06-21	2021-06-24	5 000
104579	2021-06-21	2021-07-07	25 000
104579	2021-06-21	2022-02-16	28 614
104579	2021-06-21	2022-05-03	28 617
104579	2021-06-21	2022-05-03	28 617
104579	2021-06-21	2022-11-28	28 617
104579	2021-06-21	2022-11-28	143 613
104579	2021-06-21	2023-05-02	32 200
104579	2021-06-21	2023-05-02	32 200

Expected output

ORDER_ID	TRANSACTIOIN_AMOUNT at ORDER_DATE+180
106104	600 000
104579	25 000

Appreciate any assistance on this. RGDS//Peter

[AronC]

The comprasion in you set exporession doesn't seem to be correct. Do you have ORDER_DATE and TRANSACTION_DATE in the same table in you data model. If so I would recomend to do the comparsion in the script editor like 
if(TRANSACTION_DATE <= ORDER_DATE +180, 1) as _FlagDate
Then use the flag in the set expression instead. I have used the Rank-function in similar problems, but your firstsortedvalue should also do it.

Hope it helps!

Regards

[peterstalberg · Accepted solution]

Hi Aron,

The following expression solved the problem;

sum(aggr(FirstSortedValue({<[TRANSACTION_DATE]= {"=([TRANSACTION_DATE]-[ORDER_DATE])<=180"}>} [TRANSACTION_AMOUNT], -[TRANSACTION_DATE]),ORDER_ID))

RGDS//Peter