Analytical Calendar The Story | The Index Page
Analytical Calendar
Date Range
When doing data analytics, usually, it analyzes
the data like performance or trend bounded in a specific time period. In other
words, a range of dates is selected from the calendar. There are different ways
to specify a date range including:
·
Higher Granularity Date Components
·
X to Y Date Range
·
Rolling Date Range
·
Start and End Date Range
Higher Granularity Date Components
The
design of the higher-level date components originally serves the purpose of
picking a range of dates. It is the simplest way. For example, a week, a month,
a quarter or a year, they are all groups of dates.
Usually,
the consecutive values of the same date component are picked for analysis. For
example, week 1 to week 7, Jan to Jun, etc. This is the most straightforward concept
of using a calendar with date range.
X-to-Y Date Range
To make it easy to track from similar starting to similar ending period, X-to-Y date range is always used in data analytics. For example, Year-to-Date (YTD), Year-to-Month (YTM), Year-to-Week (YTW), Month-to-Date (MTD), Week-to-Date (WTD), etc. X is a higher-level date component where Y is lower-level date component. The date represented is from the first date of X to the end date of Y. Below summarizes the typical X-to-Y date ranges.
|
X to Y Date Range |
Number of Days |
Definition |
|
Year To
Date (YTD) |
1 to 366 |
First
date of a year to a specified date within the year. |
|
Year to
Quarter (YTQ) |
90 to 366 |
First
date of a year to a specified end of year quarter within the year |
|
Year to
Month (YTM) |
31 to 366 (Addition
of 28, 29, 30, 31) |
First
date of a year to a specified end of year month within the year. |
|
Year to
Week (YTW) |
1 to 371 |
First
date of a week year to a specified end of week year week within the week
year. |
|
Quarter
to Month (QTM) |
30 to 92 |
First
date of a year quarter to a specified end of year month within the year.
quarter. |
|
Quarter
to Date (QTD) |
1 to 92 |
First
date of a year quarter to a specified date within the year quarter. |
|
Month to
Date (MTD) |
1 to 31 |
First
date of a year month to a specified date within the year month. |
|
Week to
Date (WTD) |
1 to 7 |
First
date of a week year week to a specified date within the week year week. |
Not all of them is commonly used but conceptually, all of these exist and can be applied.
Rolling Date Range
Rolling date range is another very common
concept to specify a period of time. It has three basic elements including the
rolling unit, rolling range and the specified time period. For example, rolling
12 months on 2022-Dec where month is the rolling unit, 12 is the rolling range
and 2022-Dec is the specified time period. This means the dates from 2022-Jan
to 2022-Dec. The specified time period is included in the rolling range.
Let’s take a deeper look at each of the rolling units.
|
Rolling Unit |
Number of Days |
Definition |
|
Year |
Multiple
of 365 or 366 |
Dates in
between a specific date to a year before of that date. |
|
Quarter |
Addition
of 90, 91, 92 |
Dates in
between a specific date to a quarter (i.e. 3 months) before of that date. |
|
Month |
Addition
of 28, 29, 30, 31 |
Dates in
between a specific date to a month before of that date |
|
Week |
Addition
of 1 to 7 |
Dates in
between a specific date to a week before of that date |
|
Day |
Addition
of 1 |
Dates in
between a specific date to a day before of that date |
Start and End Date Range
To be more specific, simply specify the
start and end date to select a period of time. Both the start and end date
should be in the same date granularity. It still works but it looks weird if
the granularity is not the same, e.g. 2021-Jan to 2022-03-02. It simply means
2021-Jan-01 to 2022-03-02. All needs to be in the same date granularity for
operations.
Below table summarizes the typical start and end date ranges.
|
Unique Date Range |
Number of Days |
|
[Start
Date] to [End Date] |
Addition
of 1 |
|
[Start
Quarter] to [End Quarter] |
Addition
of 90, 91, 92 |
|
[Start
Month] to [End Month] |
Addition
of 28, 29, 30, 31 |
|
[Start
Year] to [End Year] |
Addition
of 365, 366 |
|
[Start
Week Year] to [End Week Year] |
Addition
of 364 to 371 |
|
[Start
Week Year Week] to [End Week Year Week] |
Addition
of 1 |
Previous Section <== 003 - Analytical Calendar - Calendar and Date
No comments:
Post a Comment