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
