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

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

Calendar and Date Aggregation

Alteryx
Alteryx
Created on

Date aggregation has been discussed before and macros have been developed to assist with aggregating dates at various levels (e.g. month, week or year). Some of these macros even allow you to find the sum, mean, median or mode dates. See an example here.

 

Yet I’ve found the data to be incomplete or requiring additional processing to get the data the way I wanted it. Rather than copying and pasting this process from workflow to workflow, I decided to create a macro (and this macro doesn’t require any configuration!) and make it available to a wider audience with the hope that it will save time and energy by eliminating the need to recreate the process of translating a date week number, quarter, etc.

 

Data Format and Layout:

 

Essentially, it works like an input file. For all practical purposes, you can think of it as a date table with Date being the primary key.  

 

DateMacro 1.png

 

DateMacro 2.png

 

As long as you have a date in yyyy-mm-dd format in another file, you can match to this calendar table.

 

Field Definitions & Notes:

 

Date: yyyy-mm-dd format; includes every day beginning 2000-01-01 through 2099-12-31.

Year: yyyy format.

Quarter: numeric representation of quarter (1, 2, 3 or 4 rather than Q1, Q2, Q3 or Q4).

Month: numeric representation of month; NO leading zeros.

MonthName: January, February, March, April, May, June, July, August, September, October, November and December; completely spelled out rather than an abbreviation.

WeekNumber: numeric representation of week; generally values range from 1–52, but occasionally a year will have a week 53; weeks 1 and 52 (or 53) may be partial weeks (i.e. less than seven days).

Day: corresponds to the calendar date with values from 1-31 (for months with 31 days); NO leading zeros.

DayName: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday; spelled out rather than abbreviations; Sunday is the beginning of the week.

DayYear: day of year; values range from 1-365 except for leap years which have a day 366.

DayQuarter: day of quarter; values range from 1-92.

DayWeek: numeric representation of week where 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday and 7=Saturday.

Week StartDate: date in yyyy-mm-dd format and data type = Date; week begins on Sunday with the possible exception of week 1.

Week EndDate: date in yyyy-mm-dd format and data type = Date; week ends on Saturday with the possible exception of week 52 (or 53).

Month StartDate: date in yyyy-mm-dd format and data type = Date.

Month EndDate: date in yyyy-mm-dd format and data type = Date.

Quarter StartDate: date in yyyy-mm-dd format and data type = Date.

Quarter EndDate: date in yyyy-mm-dd format and data type = Date.

 

Common Use:

The attached macro/date table provides a quick and easy way to access date data you would otherwise need to calculate or decipher. Let’s say you have a file with retail transactional data. On the file, you have a date and line-item revenues, but you want to determine how much revenue was generated by week. Ordinarily, you would translate your date into a corresponding week number before you could summarize your revenue data.

 

Missing Leading Zeros:

Numeric fields (specifically, Byte and Int16 from the data format above) will not have a leading zero. In the event you need to add the leading zeros back in (the most common would be Month and Day fields), you need to change the data type to String using a Select tool and then use the following expression in a Formula tool:

 

PadLeft([FieldName], 2, '0')

 

This ensures the field has two characters. If it only has one, a zero will be added to the left (e.g. 1 becomes 01).

 

A workflow illustrating this process is attached. The macro is included in the package.

Attachments
Comments
Bolide

Very useful if you need to create a calendar table for Microsoft's PowerPivot.

Asteroid

Thanks for posting this. I'm planning on using the calendar macro but how do i join it with my data. For example i have a Property dataset with Prop Code, Property Start Date and End Date. I like to know how many properties were Active within the quarter.

 

Thanks

Jag

Alteryx
Alteryx

You can join the calendar macro to your data with a standard Join tool (join to 'Date' in the calendar macro).  In fact, you can have two joins... one for Start Date and another for End Date.  Start Date and End Date will need to be in the YYYY-MM-DD format, so you may have some data manipulation that needs to be done if your data isn't already in that format.

Asteroid

Hi,

 

I have used the calendar macro but the start week date and end week date are Showing Sunday where as I want to show week Start as Monday ( working week).

 

 

Thanks

Jag

Alteryx Certified Partner

Yes, the Monday version starts on a Sunday.

 

 

Thanks.