community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More

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

Alteryx
Alteryx
Created on

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
Meteor

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