Average monthly budget per working day

[guillaume_gorli]

Dear all,

I need to cross match daily sales value with daily sales budget.

I have a table with the budget per month. I managed to get the working day for each month and then the budget per working day for each month of the year.

Now i need to get this in a daily table so that i could compare daily actual sales and budget per working day :

What i shoud get is :

What i get in the app is :

(This because in my table the budget of January is linked with Date 31/01/2018)

Please see qvw and test excel file for the one you want to help.

Thanks in advance

Guillaume

[sunny_talwar · Accepted solution]

May be try one of these

=Sum(TOTAL Budget)

/

(NetWorkDays(DateDebutMois,DateFinMois,'01/01/2016','28/03/2016','01/05/2016','08/05/2016','05/05/2016','16/05/2016','14/07/2016','15/08/2016','01/11/2016','11/11/2016','25/12/2016','01/01/2017','17/04/2017','01/05/2017','08/05/2017','25/05/2017','05/06/2017','14/07/2017','15/08/2017','01/11/2017','11/11/2017','25/12/2017','01/01/2018','02/04/2018','01/05/2018','08/05/2018','10/05/2018','21/05/2018','14/07/2018','15/08/2018','01/11/2018','11/11/2018','25/12/2018','01/01/2019','22/04/2019','01/05/2019','08/05/2019','30/05/2019','10/06/2019','14/07/2019','15/08/2019','01/11/2019','11/11/2019','25/12/2019'))

or

=If(Sum(Sales) <> 0, Sum(TOTAL Budget)

/

(NetWorkDays(DateDebutMois,DateFinMois,'01/01/2016','28/03/2016','01/05/2016','08/05/2016','05/05/2016','16/05/2016','14/07/2016','15/08/2016','01/11/2016','11/11/2016','25/12/2016','01/01/2017','17/04/2017','01/05/2017','08/05/2017','25/05/2017','05/06/2017','14/07/2017','15/08/2017','01/11/2017','11/11/2017','25/12/2017','01/01/2018','02/04/2018','01/05/2018','08/05/2018','10/05/2018','21/05/2018','14/07/2018','15/08/2018','01/11/2018','11/11/2018','25/12/2018','01/01/2019','22/04/2019','01/05/2019','08/05/2019','30/05/2019','10/06/2019','14/07/2019','15/08/2019','01/11/2019','11/11/2019','25/12/2019')))

or this for without selections in Month and Year

=Aggr(Sum(TOTAL <Mois, Année> Budget), Date, Mois, Année)

/

(NetWorkDays(DateDebutMois,DateFinMois,'01/01/2016','28/03/2016','01/05/2016','08/05/2016','05/05/2016','16/05/2016','14/07/2016','15/08/2016','01/11/2016','11/11/2016','25/12/2016','01/01/2017','17/04/2017','01/05/2017','08/05/2017','25/05/2017','05/06/2017','14/07/2017','15/08/2017','01/11/2017','11/11/2017','25/12/2017','01/01/2018','02/04/2018','01/05/2018','08/05/2018','10/05/2018','21/05/2018','14/07/2018','15/08/2018','01/11/2018','11/11/2018','25/12/2018','01/01/2019','22/04/2019','01/05/2019','08/05/2019','30/05/2019','10/06/2019','14/07/2019','15/08/2019','01/11/2019','11/11/2019','25/12/2019'))

or this

=If(Sum(Sales) <> 0, Aggr(Sum(TOTAL <Mois, Année> Budget), Date, Mois, Année)

/

(NetWorkDays(DateDebutMois,DateFinMois,'01/01/2016','28/03/2016','01/05/2016','08/05/2016','05/05/2016','16/05/2016','14/07/2016','15/08/2016','01/11/2016','11/11/2016','25/12/2016','01/01/2017','17/04/2017','01/05/2017','08/05/2017','25/05/2017','05/06/2017','14/07/2017','15/08/2017','01/11/2017','11/11/2017','25/12/2017','01/01/2018','02/04/2018','01/05/2018','08/05/2018','10/05/2018','21/05/2018','14/07/2018','15/08/2018','01/11/2018','11/11/2018','25/12/2018','01/01/2019','22/04/2019','01/05/2019','08/05/2019','30/05/2019','10/06/2019','14/07/2019','15/08/2019','01/11/2019','11/11/2019','25/12/2019')))

[guillaume_gorli]

Thanks a lot Sunny