Good day
There is a need to calculate the stock at the end of the month
Table_stock:
| Goods | Date
| Stock_qty |
|---|
| 1 | 03.11.12 | 2 |
| 1 | 10.11.12 | 4 |
| 1 | 20.11.12 | 2 |
| 1 | 02.12.12 | 54 |
| 1 | 06.12.12 | 3 |
Calendar table:
| Date | Month | Week |
|---|
| 01.11.12 | November | 34 |
| ... | ... | ... |
| 06.12.12 | December | 40 |
Pivot Table
| Goods | Month | Stock qty |
|---|
| 1 | november | 2 |
| 1 | December | 3 |
I tried a set analysis and if
Sum({<Date={'$(=Max(Date))'}>} Stock)
Aggr(Sum({<Date={'$(=Max(Date))'}>} Stock),Goods,Month)
Sum({<Date={'$(=Aggr(Max(Date),Month))'}>} Stock)
Sum(if(Date=Max(Total Date),Stock))
