Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')))
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')))
Thanks a lot Sunny