Alteryx Designer

Definitive answers from Designer experts.

Building A Macro to Calculate Week of the Year

Alteryx Alumni (Retired)
Created

 

Macros Save Time 

 

One of the best time savings tactics for a developer is to create their own macros for repetitive processes.  In the case of this macro, an Alteryx user may need to know what week of the year an event falls on.  For example, how many transactions occurred during the 23rd week of the year? Accomplishing this task requires formula tools, time/date fields, and come clever parsing to account for leap years.

 

The first step is to separate the date into its component parts.  By separating the date into its component parts, a calculation can be done later in a formula tool that will create a date for January 1st of the year of that specific record.

 

 

datemacro.JPG

 

 

 

The next step is to use a Formula tool to divide the [Month] and [Day] fields by themselves, to create a 1/1 date.  This date will be compared against the record date to determine how many weeks it has been since the start of the year.

 

 

 

 

 

datemacro2.JPG

 

 

 


The final step requires several formulas in one Formula Tool to complete the process. Doing math using the Date formula calculations in a Formula tool requires a specific formatting.  The first two formulas determine whether the month is a two digit month or a single digit month. The following two formulas detect whether or not a leading 0 is necessary for proper date formatting and inserts it. The fifth formula brings the components together to create the properly formatted yyyy-01-01 date to compare the record date to.  The sixth and final formula determines how many days have passed from January 1st to the record date, divides by 7, and rounds up.  The result is the week of the year that the record fell on.

 

 

 

 

datemacro3.JPG

 

 

 

 

There we have it!  Please find the formulas and configurations in the attached workflow.

 

 

Attachments
Comments
Alteryx Partner

Hey @PaulT 

I'm looking for some Macro Development advice and hoping you may take a look at my post over here and potentially chime in.

Alteryx Partner

@PaulT It seems I was able to answer my own question.