Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I had 2 tables Budget and Sales
where as in budget table having fields like code, jan,Feb,Mar,...,Dec, etc....
Where as in Sales table having fields like code, Date,Sales,etc....
My requirement is to calculate MTD,YTD for sales and Invested_Amount based on Year and Month selections.
I had used Cosstable for Budget and created master calendar based on date field in Sales table.
but when I select year and month MTD for sales given accurate results. but for Invested_Amount not given proper results.
Sales :
Code | Description | Date | Amount |
1019111 | Roadways | 01/01/2019 | 10000 |
1019222 | Railways | 01/01/2019 | 11000 |
1019333 | Airlines | 05/05/2019 | 12000 |
1019444 | sea transport | 06/07/2019 | 13000 |
1019111 | Roadways | 05/02/2019 | 14000 |
1019222 | Railways | 04/03/2019 | 15000 |
1019444 | sea transport | 20/07/2019 | 16000 |
Budget:
Code | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
1019111 | 50000 | 100000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 |
1019222 | 50000 | 100000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 |
1019333 | 50000 | 100000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 |
1019444 | 50000 | 100000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 | 50000 |
Expected O/P: When selected filters Year = 2019 , Month = Feb
Code | Description | Month-to-date Sales | Month-to-date Budget | year-to-date Sales | year-to-date Budget |
1019111 | Roadways | 14000 | 100000 | 24000 | 150000 |
1019222 | Railways | 0 | 100000 | 11000 | 150000 |
1019333 | Airlines | 0 | 100000 | 0 | 150000 |
1019444 | sea transport | 0 | 100000 | 0 | 150000 |