Calculating Customized Week number

[vshekharrc]

Hi All,

Please see the attached excel file. It has three columns, ReocrdID, Date and calculatedweek. I have a series of dates worth of 1 year for my project. The project start date is 2019-07-01(July1, 2019) through 2020-06-30 (June 30, 2020). I want to create a workflow that populated "calculatedweek" column as shown by the field "calculatedweek"(from 1 to 52). so for example,  dates from 2019-07-01 through 2019-07-06 should have a value of calculatedweek = 1, dates from 2019-07-07 through 2019-07-13 will have a value of calculatedweek = 2 and so on. So every week number will have 7 days. The last few dates after week 52 should be seen as week 52 itself. So please notice that last 9 dates has calculatedweek = 52

Could you please let me know how do I perform this in alteryx. It would be very helpful if you could attach your workflow as well.

Regards

Varun Shekhar

[Qiu · Accepted solution]

@vshekharrc 

Something like this?

And the last two days should belong to week 53 I think.

[Emil_Kos · Accepted solution]

Hi @vshekharrc,

I have used the date-time diff formula in order to calculate the week number

The output:

[grazitti_sapna · Accepted solution]

Hi @vshekharrc,

Here is my take on this problem using formula tool.

If it solves your problem please it as the solution.

[vshekharrc]

Hi @Qiu ,

Thank you for getting this!. yes, factually it should be 53 as you mentioned, but anything greater than 52 should be regarded as 52 in my work. I will correct that with a simple update.

Regards

Varun Shekhar

[vshekharrc]

Hi @Emil_Kos 

This is a great solution where you have made use of the date column to retrieve the week. Thank you very much! I learnt a couple of new tools and functions in Alteryx!

Best,

Varun Shekhar

[vshekharrc]

Hi Sapna,

Thank you very much. This solution also addresses my question.

Regards

Varun Shekhar