Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
Try with FirstSortedValue functionality
=FirstSortedValue(Stock, -num(Date))
Hope it helps
Thanks for the answer, but it does not work for me
This solved my similar problem! Thanks!