convert week to Month

[v12dbs]

Dear gurus,

I have a year column (with only 2017) and week column. The data doesnt have date column. However, I need to convert weeks to respective months. How should I do it in ALteryx? Moreover, 

week 5 is Jan 30 to Feb 5. So how should I convert the week number to two months?

Year     Week    Sales

2017       5          200

2017       5          100

2017       6          300

2017       9          400

Your help in this matter will be sincerely appreciated. 

[danrh · Accepted solution]

Something like this should do the trick in a Formula tool:

DateTimeFormat(DateTimeAdd(ToString([Year])+'-01-01',[Week]*7-1,'day'),'%m')

Make minor modifications as needed to make sure the weeks land where you want them to.  In the above, it puts the week in the month where the week ends.

As far as how to split between months, that depends a lot on your data and how you want it to function.  If it needs to be proportionally split (i.e. 2 days in one month means that month gets 2/7 of the week's sales) or if it needs to go to the month where the week starts/ends, etc.  If you can add some more requirements I'd be happy to take a look.

Hope it helps!