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