Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I got a data set and I am calculating the Variance of Actual vs Budgeted Sales.
Assume the financial year is from July 1st to June 30th of the following year. Please refer the below sample data set for product A's sales details. (For simplicity, I am recording the sales values at the end of each month).Product A Sales | |||
Product_ID | Sales Month | Actual Sales | Budgeted Sale |
P_100 | 31/July/2017 | 100 | 120 |
P_100 | 31/August/2017 | 115 | 125 |
P_100 | 30/September/2017 | 150 | 125 |
P_100 | 31/October/2017 | 125 | 120 |
P_100 | 30/November/2017 | 120 | 115 |
P_100 | 31/December/2017 | 135 | 120 |
P_100 | 31/January/2018 | 120 | 125 |
P_100 | 28/February/2018 | 140 | 125 |
P_100 | 31/March/2018 | 110 | 125 |
P_100 | 30/April/2018 | 105 | 125 |
P_100 | 31/May/2018 | 125 | 130 |
P_100 | 30/June/2018 | 120 | 120 |
P_100 | 31/July/2018 | 125 | 125 |
P_100 | 30/August/2018 | 130 | |
P_100 | 30/September/2018 | 130 | |
P_100 | 31/October/2018 | 120 | |
P_100 | 30/November/2018 | 120 | |
P_100 | 31/December/2018 | 130 | |
P_100 | 31/January/2019 | 125 | |
P_100 | 28/February/2019 | 130 | |
P_100 | 31/March/2019 | 125 | |
P_100 | 30/April/2019 | 125 | |
P_100 | 31/May/2019 | 130 | |
P_100 | 30/June/2019 | 125 |
The above data set got two years of data and my requirements are;
1. When I open the app, I want to see the below table information about the sales for Product A.
Calculate the sum of actual Sales, sum of budgeted sales and variance as shown below from 31/July/2017 to 31/July/2018.
Product_ID | Actual Sales | Budgeted Sales | Variance |
P_100 | 1465 | 1475 | 10 |
Note
When adding the budgeted sales values, I want to display the sum of budgeted sales from 31/July/2017 to 31/July/2018 and the variance also should be from 31/July/2017 to 31/July/2018 (last month).
I am using a master calendar in my app.
2. Assume I got Year and the respective month on the app visualization window using a filter pane so that a user can select a fiscal year and a month to visualize the respective Year's and Month's data. Suppose a user selects year as 2018 and the month as January, the above table should depict the sales information for the selected criteria as shown below,
Product_ID | Actual Sales | Budgeted Sales | Variance |
P_100 | 120 | 125 | 5 |
Could anyone help me with the above which I wanted to achieve since some time.
Appreciate, even someone could help me to accomplish at least part of the tasks.
Thanks in advance and it would be a great help for me.
Kind regards,
Andy
Hi Paola,
Thanks a lot for your reply.
If you don't mind, could you please send me the .qvf file for me to go through?
Thanks a lot.
Kind regards,
Andy
I attached my modified app in my last post.
I have attached it also in this reply.
Here I have changed file name (SumOfCost2), so there could not be confusion.
Hi Paola,
Sorry for the inconvenience created.
I saw the attached document later and apologies for that.
Kind regards,
Andy