Hi
I have to create the following logic in the expression not in the script in a straight / Pivot table.
1. Retrieve all the rows where CREATION_DATE is less than the User Selected date AS_DATED.
(Not shown in the sample data). AS_DATED is a filter in the sheet from where user can select a date.
2. Then for each LNUMBER, Select the distinct TERM IDs available.
3. For each distinct TERM_IDs then select the MAX Version. There may be multiple rows with max version.
4. Then SUM the amount.
In the example below the output should be the last 3 lines. Sum Of Amount should be (16.5+ (-74.93) + .54)= - 57.89
| ID |
LNumber |
Flag |
Version |
Term ID |
Created Date |
Type |
Short Date |
AMOUNT |
| 2245 |
VU66PWY |
N |
4481 |
2245 |
14-Jan-21 |
AMORTIZATION |
01-04-2021 |
16.5 |
| 2245 |
VU66PWY |
N |
5035 |
2245 |
14-Jan-21 |
AMORTIZATION |
01-04-2021 |
16.5 |
| 2245 |
VU66PWY |
N |
5035 |
78244 |
20-Apr-21 |
AMORTIZATION |
30-04-2021 |
-74.93 |
| 2245 |
VU66PWY |
N |
5035 |
2245 |
20-Apr-21 |
AMORTIZATION |
30-04-2021 |
0.54 |
Can you please help?
Many Thanks
Ankhi
