Important Community update: The process for changing your account details was updated on June 25th. Learn how this impacts your Community experience and the actions we suggest you take to secure your account here.

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Creating Period Number from Weeks of Month

Adarsh_R3
8 - Asteroid

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-202101
15-02-202102
26-02-202103
20-03-202103
05-04-202104
21-04-202105

 

Adarsh_R3_0-1635854143390.png

 

 

 

5 REPLIES 5
Luke_C
17 - Castor

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. 

 

Luke_C_0-1635856740034.png

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@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

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Christina_H
14 - Magnetar

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
8 - Asteroid

Thanks, @MarqueeCrew  for your reply... 

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

Adarsh_R3
8 - Asteroid

Thanks, @Christina_H for your reply.

Labels