Monthly FY21 data to Weekly

[Usamah22]

Hello,

I have monthly data for FY2020-2021 as below and want to convert it to weekly. how would i do this considering there are different numbers of weeks in each month? I need this going up all the way to end of March 21.

group	01/04/2020	01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020
a	£584	£597	£605	£599	£592	£602
b	£981	£1,005	£1,018	£1,008	£994	£1,012
c	£407	£417	£420	£417	£411	£417

[PhilipMannering]

Strictly speaking you can't. As you say, weeks and months don't align perfectly.

You could estimate the weekly numbers based on the number of days that fall into each month, but the information will become more "fuzzy".

[Jonathan-Sherman]

Hi @Usamah22,

You could break down each month into a daily level (using generate rows) taking the value in the month and dividing by the number of days to give you a daily value. Changing the format of the date to a weekly level would give you the weekly value. However as @PhilipMannering this does get a little "fuzzy".

On a side note, i've started my week on Monday. Therefore Week 13 in your data set is 2020-04-01 (Wednesday) through to 2020-04-05 (Sunday) and Week 14 is 2020-04-06 (Monday) through to 2020-04-12 (Sunday).

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Usamah22]

I've tried to put the below data in but the total numbers do now add up?

Group	01/04/2020	01/05/2020	01/06/2020	01/07/2020	01/08/2020	01/09/2020	01/10/2020	01/11/2020	01/12/2020	01/01/2021	01/02/2021	01/03/2021
a	1640.9299	1677.24855	1700.36177	1682.56448	1661.92031	1691.6524	1732.98005	1700.78295	1713.94126	1681.74231	1585.98091	1702.03753
b	2755.53251	2823.72407	2860.35214	2830.30973	2791.53814	2841.34779	2919.07397	2861.91291	2886.65566	2829.49209	2649.36799	2857.43221
c	1142.10044	1172.01013	1180.3644	1171.03059	1154.01317	1171.92634	1206.01609	1184.78765	1195.63445	1174.406	1095.38009	1175.0777
d	1606.55681	1651.22238	1663.81455	1649.81794	1624.40518	1648.41355	1699.23988	1667.53112	1683.70331	1651.9945	1534.17073	1653.17112
e	87.8726241	90.1682895	90.8341688	90.1054774	88.7993725	90.0426714	92.6549792	91.0159438	91.847154	90.208114	84.1524182	90.2966515
t	4864.22977	4676.08413	5240.52105	5627.77384	5063.33691	5439.6282	5837.84248	5649.69684	5096.22141	5859.76548	5859.76548	6424.20241

[Jonathan-Sherman]

Are you wanting to divide the total by all days in the month or only week days?

[Usamah22]

Only by weekdays preferably.

[Jonathan-Sherman · Accepted solution]

Hi @Usamah22,

Balances now (bar a few pounds because of rounding)

If this solves your issue please mark the answer as correct, if not let me know! I've attached an updated workflow for you to download if needed.

Regards,

Jonathan

[Usamah22]

thanks that works!