Creating Period Number from Weeks of Month

[Adarsh_R3]

Hi everyone,

Could anyone please help me with the logic in creating Period numbers from specific weeks?

For instance, if the Date is 15-01-2021 then the Period num should be 1 or if the Date is 27-01-2021 then the Period num should be 2 as mentioned in the below screenshot.

The below-mentioned table is the result I want but am not able to arrive at the logic.

Date	Period Number
28-01-2021	01
15-02-2021	02
26-02-2021	03
20-03-2021	03
05-04-2021	04
21-04-2021	05

[Luke_C · Accepted solution]

Hi @Adarsh_R3 

Here's an example that I think does what you're looking for, or at least should get you in the right direction. Your calendar screenshot appears to be from 2020 while your sample data is 2021, but I made the following assumptions based on it:

1. Periods are 4 weeks
2. Mondays mark the first day of the week

From there, I did the following:

1. Generate rows to create a record for every day of the year
2. Added a field for day of the week
3. Used a multirow formula tool to assign each day into a week, based on Mondays
4. Used a multirow formula tool to assign the weeks to periods, based on if the current week/prior week was evenly divisible by 4. 

[MarqueeCrew]

@Adarsh_R3 ,

I like the question asking about "logic" rather than please solve this for me.  Thank you for asking about the approach to solving the problem.

Let's start with the dates.  Alteryx uses "YYYY-MM-DD" formats from ISO standards and your dates need to be reformatted.  The FORMULA tool is your friend.  Yes.  You could use a date-time tool to adjust your dates, but let's see the real formula:

DateTimeParse([Date Field], "%d-%m-%Y")

This parses your date into day-month-YEAR format.  If you use your global search and look for datetimeparse, you'll find the quantifiers that will explain all sorts of date options.  You can clean up the Date field with a SELECT and change the type to "DATE" (you'll be happier later).

Now, we need to handle ranges.  You'll need to reformat the table (with the adjusted dates to ISO format).  If you use a MULTI ROW FORMULA, you can create [EndDate] variables by using a formula like:

MIN(DateTimeAdd([Row+1:Date],-1,"Days"),"2022-12-31")

This will calculate the prior day to the next row.  For the last row it won't be able to find a date and the answer will be NULL.  In order to have a date there, we can specify the end date as a constant (e.g. 2022-12-31) and use it in a nested calculation where the minimum date is chosen between the calculated date and the maximum date.  The maximum date only gets selected at the end of the process.

Now comes for the challenge!  You need to "generate" a record for each date in the range.  Alteryx doesn't have a range function.  So you'll generate a record for every date and use it in a JOIN.  On the gallery (will move to community in the future of this post), I have a tool to generate date rows but will show you how to do it with a GENERATE ROWS tool.

The GENERATE ROWS tool has 4 configuration options.  You'll need to update the DATE for every row to be the series of dates and then calculate +1 days for every record.  This will keep the appropriate period for the final matching.

• Update Existing Field:   Choose DATE
• Initialization Expression:  [Date] 
• Condition Expression:  

[Date] < [EndDate]

• Loop Expression:

DateTimeAdd([Date],1,"Days")

Now you can join your "Data" to the table and append the Period.  To do this, I'd use the FIND REPLACE tool (instead of a JOIN) and have the data go into the FIND (upper) anchor and the table go into the REPLACE (lower) anchor.  Note if you are team vertical like ACE and Grand Prix Winner @jarrod , it will be the LEFT and RIGHT anchor.  Using the FIND REPLACE tool will be faster and will output every record in sequence.  If you have a date that isn't handled in the lookup, it will get a NULL value.  So configure the tool to FIND the data date and Find the DATE field in the table.  Look for the Entire Field.  In the replace configuration, you'll APPEND FIELDS and only select the [Period] field.

Cheers,

Mark

[Christina_H]

I took a simpler approach to this.  Assuming you're trying to create 4 week periods, use a formula similar to this:

CEIL(DateTimeDiff([DateTime_Out],'2021-01-01',"day")/28)

DateTime_Out is the result of converting your original date into a date format.  Then enter the day before the start of period 1 as the second date.  It should then generate the correct period number.

[Adarsh_R3]

Thanks, @MarqueeCrew  for your reply... 

Please post more videos on your channel, great content there. 

[Adarsh_R3]

Thanks, @Christina_H for your reply.