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)
