This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
WeekStartDate: date in yyyy-mm-dd format and data type = Date; week begins on Sunday with the possible exception of week 1.
WeekEndDate: date in yyyy-mm-dd format and data type = Date; week ends on Saturday with the possible exception of week 52 (or 53).
MonthStartDate: date in yyyy-mm-dd format and data type = Date.
MonthEndDate: date in yyyy-mm-dd format and data type = Date.
QuarterStartDate: date in yyyy-mm-dd format and data type = Date.
QuarterEndDate: date in yyyy-mm-dd format and data type = Date.
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.