Calculate sum of costs for variable number of months

[andymanu]

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

[andymanu]

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

[paola_valenti]

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.

[andymanu]

Hi Paola,

Sorry for the inconvenience created.

I saw the attached document later and apologies for that.

Kind regards,

Andy