Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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