Take value for last date for each period ?

[ok5454564]

Hi,

I have 3 tables :

Table 1

Year	Year_after
2016	0
2016	1
...	...
2016	6
...	...
2022	0

Table 2

Contract	Starting year
C1	2016
C2	2016
C3	2018

Table 3 :

Invoice	Contract	Amount	Date
I1	C1	50	30/10/2016
I2	C1	100	30/11/2017
I3	C1	200	31/12/2017
I4	C2	250	01/08/2017
I5	C3	300	31/10/2018

What i want is something like an aggr(... , Year, Year_after) with the sum of all amount of last invoice (sorting by date) for each contract of the given year. Here it will give :

Final table

Year	Year after	LastDayPeriod	Amount
2016	0	31/12/2016	50
2016	1	31/12/2017	450
2016	2	31/12/2018	450
2017	0	31/12/2017	0
2017	1	31/12/2018	0
2017	2	31/12/2019	0
2018	0	31/12/2018	300
2018	1		300

I was thinking about firstsortedvalue but something didnt work when i use aggr and firstsortedvalue, i have no result at the end :

Aggr(FirstSortedValue(Amount,if(Date<=LastDayPeriod,-Date,0)),Year, Year_after)

[ok5454564]

Nobody can help me ?