Alteryx Designer

Definitive answers from Designer experts.

How To: Calculate the week number, quarter, month start, month end

Alteryx
Alteryx
Created

Alteryx has the ability to calculate the quarter, week number, etc:

 

Example 

The attached workflow uses several of the Date Time functions:  (See attached workflow)

  • DateTimeFirstOfMonth() Returns the first day of the current month, at midnight.
  • DateTimeLastOfMonth() Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).
  • Quarter formula - if [Month] IN (1,2,3) THEN [Year]+' '+'Q1' elseif [Month] IN (4,5,6) THEN [Year]+' '+'Q2' elseif [Month] IN (7,8,9) THEN Year]+' '+'Q3' else [Year]+' '+'Q4' endif
  • Week Number - ToNumber(DateTimeFormat([Date],'%U'))+1

 

Input:
week_number.JPG
 
Output:
week_number_2.JPG
 

Visit our help documentation for detailed information about Date Time Functions

Attachments
Comments
7 - Meteor

it would be so helpful to be able to add any date to this function and get the monthend:  DateTimeLastOfMonth(1/15/2017).