Analytical Calendar The Story | The Index Page
2023-05-14
Analytical Calendar
Calendar Considerations and Standardization
For humans, the calendar and date design are
intuitive and easy to use even though there are a few different methods to
manipulate the date.
On the contrary for analytical tool
development, it is difficult for computers to understand date and calendar
concepts because there are many different rules based on various situations. Amongst
the rules, there are always contradictions and different methods of handling
are needed.
To make it easier for manipulation, there
should be a single standard. It not only facilitates the computer for
development but also it would be adaptable for human use as well.
As aforementioned, there are date
uniqueness, date granularity, date hierarchy, date range and date comparison. There
should be a way that all these concepts can work together in a systematic and
coherent manner.
The standardization will be based on this
concept for all the considerations.
Date Integrity and Ambiguity
Before understanding how to work out the
standard, we will first need to understand more on the date integrity and
ambiguity.
Date has integrity and integrity means the
quality of having strong principles that are followed at all times. Take rolling unit as an example. It has the
values of year, quarter, month, week and day. However, it cannot be applied to
all the situations. If the specified time period is of different granularity. It
is illustrated below.
|
Rolling Unit |
Date Granularity
of Specified Time Period |
||||
|
Year |
Quarter |
Month |
Week |
Day |
|
|
Year |
✔ |
✔ |
✔ |
✔ |
✔ |
|
Quarter |
✖ |
✔ |
✔ |
✖ |
✔ |
|
Month |
✖ |
✖ |
✔ |
✖ |
✔ |
|
Week |
✖ |
✖ |
✖ |
✔ |
✔ |
|
Day |
✖ |
✖ |
✖ |
✖ |
✔ |
It can be observed that the rolling unit is
having the same or higher level of date granularity of specified time period. And
it should be within the same hierarchy.
Imagine that it is now 2021-Feb, let’s have
a look at three scenarios:
1.
Rolling for 2 days (Lower
granularity level)
First, convert all the month into date
and result in total 28 days from 2021-02-01 to 2021-02-28. So, all these days
will roll for 2 days, i.e. 2021-01-30 to 2021-02-28. In total, there are now 30
days. The date granularity is changed from month to date. It creates confusion.
The result is no longer be able to express in month level anymore. It should be
avoided and thus the table shown before is not ticked for this combination.
2.
Rolling for 2 months (Same
granularity level)
It results in 2021-Jan to 2022-Feb. There
are two months and still in the month level.
3.
Rolling for 2 years (Higher
Granularity level)
First, convert 2 years into 24 months. It
results in 2020-Mar to 2022-Feb, i.e. 24 months and still in month level.
Ambiguous situations always happen when there
is a change of date granularity. There might be confusion when converting the
date into lower or higher granularity. More
importantly, the date conversion should be within the hierarchy.
Similar situations happen on different
scenarios below.
For comparison on date components:
|
Comparison Unit |
Date Granularity |
||||
|
Year |
Quarter |
Month |
Week |
Day |
|
|
Year |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Quarter |
✔ |
✔ |
✖ |
✖ |
✖ |
|
Month |
✔ |
✔ |
✔ |
✖ |
✖ |
|
Week |
✔ |
✖ |
✖ |
✔ |
✖ |
|
Day |
✔ |
✔ |
✔ |
✔ |
✔ |
For comparison on X-to-Y date range:
|
Date Range |
Comparison Unit |
||||
|
Year |
Quarter |
Month |
Week |
Day |
|
|
Year To
Date (YTD) |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Year To
Quarter (YTQ) |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Year to
Month (YTM) |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Year to
Week (YTW) |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Quarter
to Month (QTM) |
✔ |
✔ |
✖ |
✖ |
✖ |
|
Quarter
to Date (QTD) |
✔ |
✔ |
✖ |
✖ |
✖ |
|
Month to
Date (MTD) |
✔ |
✔ |
✔ |
✖ |
✖ |
|
Week to
Date (WTD) |
✔ |
✖ |
✖ |
✔ |
✖ |
For comparison on rolling date range
|
Date Granularity of Specified Time Period |
Rolling Unit |
Comparison Unit |
||||
|
Year |
Quarter |
Month |
Week |
Day |
||
|
Year |
Year |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Quarter |
Year |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Quarter |
✔ |
✔ |
✖ |
✖ |
✖ |
|
|
Month |
Year |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Quarter |
✔ |
✔ |
✖ |
✖ |
✖ |
|
|
Month |
✔ |
✔ |
✔ |
✖ |
✖ |
|
|
Week |
Year |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Week |
✔ |
✖ |
✖ |
✔ |
✖ |
|
|
Day |
Year |
✔ |
✖ |
✖ |
✖ |
✖ |
|
Quarter |
✔ |
✔ |
✖ |
✖ |
✖ |
|
|
Month |
✔ |
✔ |
✔ |
✖ |
✖ |
|
|
Week |
✔ |
✖ |
✖ |
✔ |
✖ |
|
|
Day |
✔ |
✔ |
✔ |
✔ |
✔ |
|
Standard Date Representation
As aforementioned, there are different ways
to represent date range including higher date granularity components, X-to-Y
date range, rolling date range as well as start/end dates. In fact, no matter
which date / date range representation is used, the representation can be
standardized into start date and end date representation. Below it converts all
of them into start date and end date representation.
For higher date granularity component:
|
Date Granularity |
Start Date |
End Date |
|
Year |
YYYY-01-01 |
YYYY-12-31 |
|
Quarter |
For Quarter 1, YYYY-01-01 For Quarter 2, YYYY-04-01 For Quarter 3, YYYY-07-01 For Quarter 4, YYYY-10-01 |
For Quarter 1, YYYY-03-31 For Quarter 2, YYYY-06-30 For Quarter 3, YYYY-09-30 For Quarter 4, YYYY-12-31 |
|
Month |
For 1,
YYYY-01-01 For 2,
YYYY-02-01 For 3,
YYYY-03-01 For 4, YYYY-04-01 For 5,
YYYY-05-01 For 6,
YYYY-06-01 For 7,
YYYY-07-01 For 8,
YYYY-08-01 For 9,
YYYY-09-01 For 10,
YYYY-10-01 For 11,
YYYY-11-01 For 12,
YYYY-12-01 |
For 1,
YYYY-01-31 For 2,
YYYY-02-28 (for common year)/29 (for leap year) For 3,
YYYY-03-31 For 4, YYYY-04-30 For 5,
YYYY-05-31 For 6,
YYYY-06-30 For 7,
YYYY-07-31 For 8,
YYYY-08-31 For 9,
YYYY-09-30 For 10,
YYYY-10-31 For 11,
YYYY-11-30 For 12, YYYY-12-31 |
|
Week |
For Incomplete Week Model YYYY-01-01 or each Sunday in YYYY For Complete Week Model Each Sunday in YwYwYwYw * Assume Sunday is the first day of week. |
For Incomplete Week Model Each Saturday in YYYY or YYYY-12-31 For Complete Week Model Each Saturday in YwYwYwYw * Assume Sunday is the first day of week. |
For X-to-Y date range:
|
X to Y Date Range |
Start Date |
End Date |
|
Year To
Date (YTD) |
YYYY-01-01 |
Any date
in YYYY-01-01 to YYYY-12-31 |
|
Year To
Quarter (YTQ) |
YYYY-01-01 |
Either
one of the below: 1. YYYY-03-31 2. YYYY-06-30 3. YYYY-09-30 4. YYYY-12-31 |
|
Year to
Month (YTM) |
YYYY-01-01 |
1. YYYY-01-31 2. YYYY-02-28/29 3. YYYY-03-31 4. YYYY-04-30 5. YYYY-05-31 6. YYYY-06-30 7. YYYY-07-31 8. YYYY-08-31 9. YYYY-09-30 10. YYYY-10-31 11. YYYY-11-30 12. YYYY-12-31 |
|
Year to
Week (YTW) |
For Allow Incomplete Week YYYY-01-01 For Only Complete Week The first
Sunday in YwYwYwYw * Assume
Sunday is the first day of week. |
For Allow Incomplete Week 1. YYYY-12-31 2. Or any Saturday in YYYY For Only Complete Week Any
Saturday in YwYwYwYw * Assume
Sunday is the first day of week. |
|
Quarter
to Month (QTM) |
1. YYYY-01-01 2. YYYY-04-01 3. YYYY-07-01 4. YYYY-10-01 |
1a. YYYY-01-31 1b. YYYY-02-28/29 1c. YYYY-03-31 2a. YYYY-04-30 2b. YYYY-05-31 2c. YYYY-06-30 3a. YYYY-07-31 3b. YYYY-08-31 3c.
YYYY-09-30 4a. YYYY-10-31 4b. YYYY-11-30 4c.
YYYY-12-31 |
|
Quarter
to Date (QTD) |
1. YYYY-01-01 2. YYYY-04-01 3. YYYY-07-01 4. YYYY-10-01 |
1. YYYY-01-01 to YYYY-03-31 2. YYYY-04-01 to YYYY-06-30 3. YYYY-07-01 to YYYY-10-30 4. YYYY-10-01 to YYYY-12-31 |
|
Month to
Date (MTD) |
1. YYYY-01-01 2. YYYY-02-01 3. YYYY-03-01 4. YYYY-04-01 5. YYYY-05-01 6. YYYY-06-01 7. YYYY-07-01 8. YYYY-08-01 9. YYYY-09-01 10. YYYY-10-01 11. YYYY-11-01 12. YYYY-12-01 |
1. YYYY-01-01 to YYYY-01-31 2. YYYY-02-01 to YYYY-02-28/29 3. YYYY-03-01 to YYYY-03-31 4. YYYY-04-01 to YYYY-04-30 5. YYYY-05-01 to YYYY-05-31 6. YYYY-06-01 to YYYY-06-30 7. YYYY-07-01 to YYYY-07-31 8. YYYY-08-01 to YYYY-08-31 9. YYYY-09-01 to YYYY-09-30 10. YYYY-10-01 to YYYY-10-31 11. YYYY-11-01 to YYYY-11-30 12. YYYY-12-10 to YYYY-12-31 |
|
Week to
Date (WTD) |
For Allow Incomplete Week 1. YYYY-01-01 2. Any Sunday in the YYYY For Only Complete Week Each
Sunday in YwYwYwYw * assume
Sunday is the first day of week. |
For Allow Incomplete Week Any date
in the week. For Only Complete Week Any date
in the week * assume
Sunday is the first day of week. |
For rolling date range:
Assuming YYYY, YYYY-QQ, YYYY-MM, YwYwYwYw
-WW, YYYY-MM-DD as specified date for each date granularity component and N as
the rolling range.
|
Date Granularity |
Rolling Unit |
Start Date |
End Date |
|
Year i.e. YYYY |
Year |
N-1 year before YYYY-01-01 |
YYYY-12-31 |
|
Quarter i.e. YYYY-QQ |
Year |
N-1 year before YYYY-QQ |
End date of YYYY-QQ |
|
Quarter |
N-1 quarter before YYYY-QQ |
End date
of YYYY-QQ |
|
|
Month i.e. YYYY-MM |
Year |
N-1 year before YYYY-MM-01 |
End date of YYYY-MM |
|
Quarter |
(N-1 ) * 3 months before
YYYY-MM-01 |
End date of YYYY-MM |
|
|
Month |
N-1 month before YYYY-MM-01 |
End date of YYYY-MM |
|
|
Week i.e. YwYwYwYw -WW |
Year |
Start date in the week of N-1
year before YwYwYwYw -WW |
End date of YwYwYwYw -WW |
|
Week |
Start date in the week of N-1 week before YwYwYwYw -WW |
End date of YwYwYwYw -WW |
|
|
Date i.e. YYYY-MM-DD |
Year |
N-1 year before YYYY-MM-DD |
YYYY-MM-DD |
|
Quarter |
(N-1) *3 months before YYYY-MM-DD |
YYYY-MM-DD |
|
|
Month |
N-1 months before YYYY-MM-DD |
YYYY-MM-DD |
|
|
Week |
(N-1) * 7 days before YYYY-MM-DD |
YYYY-MM-DD |
|
|
Day |
N-1 day before YYYY-MM-DD |
YYYY-MM-DD |
As a result, there is only one unique start
and end date representation. Although it does not help human beings, it does
help computers as a standardized representation. Thus, to facilitate this to
human, there is a few more design requirements that will be discussed in coming
sections.
Sequence Standardization
Date in each date granularity, in fact, can
be represented in sequence and it iterates 1 by the date unit. In date
calculation, it is always required to convert into the same date granularity
before any calculation can take place.
For easy and fast calculation, sequence
plays an important role - for any calculation, it simply just adds / deducts
the number of date units required. For any date, it can have the same or above
level date sequences. For example, Year month has year sequence, year quarter
sequence and year month sequence. However, it does not have date sequence or week
year week sequence because it does not possess the more granular date
information.
Year Sequence
Year sequence is quite simple because year
itself is already a sequence. No calculation is required.
Year Quarter Sequence
Year quarter sequence can simply be calculated by Year * 4 + quarter. For example, 2022 Q1, the calculation is 2022 * 4 + 1 = 8089. Below is a summarized table for easy spot of the sequence.
|
Year Quarter |
Year Sequence |
|
2022 Q3 |
8091 |
|
2022 Q2 |
8090 |
|
2022 Q1 |
8089 |
|
2021 Q4 |
8088 |
|
YYYY-QQ |
YYYY * 4
+ QQ |
Year Month Sequence
Year month sequence can simply be
calculated by Year * 12 + month. For example,
2022-10, the calculation is 2022 * 12 + 10 = 24274. Below is a summarized table for easy spot of
the sequence.
|
Year Month |
Year Sequence |
|
2022-Oct |
24274 |
|
2022-Sep |
24273 |
|
2022-Aug |
24272 |
|
2022-Jul |
24271 |
|
2022-Jun |
24270 |
|
2022-May |
24269 |
|
2022-Apr |
24268 |
|
2022-Mar |
24267 |
|
2022-Feb |
24266 |
|
2022-Jan |
24265 |
|
YYYY-MM |
YYYY * 12
+ MM |
Week Year Week Sequence
Week Year Week sequence consists of two
models including:
·
Incomplete Week Model
The sequence calculation is year * 53 +
week. For example, 2022-W02, the calculation is 2022 * 53 + 2 = 107186.
·
Complete Week Mode
It takes the difference between the date
and the first Sunday in the week of 1900-01-01, i.e. 1989-12-31 and then take
the floor of difference divided by 7. i.e. Floor((Date – “1989-12-31”)/ 7). This
can be applied in both the
Below is a summarized table for easy spot
of the sequence.
|
Year Month |
Year Sequence (Incomplete
Week Model) |
Year Sequence (Complete Week
Model) |
|
2022-W5 |
107171 |
6340 |
|
2022-W4 |
107170 |
6369 |
|
2022-W3 |
107169 |
6368 |
|
2022-W2 |
107168 |
6367 |
|
2022-W1 |
107167 |
6366 |
|
2021-W53 |
107166 |
6365 |
|
YYYY-WWW |
YYYY * 53
+ WWW |
Floor((Date
– “1989-12-31”)/ 7) |
Date Sequence
Date sequence can simply be calculated by the number of days in difference between YYYY-MM-DD and 1900-01-01 plus 2 days.
|
Year Month |
Year Sequence |
|
2022-01-05 |
44566 |
|
2022-01-04 |
44565 |
|
2022-01-03 |
44564 |
|
2022-01-02 |
44563 |
|
2022-01-01 |
44562 |
|
2022-12-31 |
44561 |
|
YYYY-MM-DD |
Number of
days in difference between YYYY-MM-DD and 1900-01-01 plus 2 days |
Uniqueness with Date Granularity
Integrity also happens in date uniqueness
and date granularity. For example, a unique date 2022-02-01, it is with the day
granularity. A unique month 2022-02, it
is with month granularity, etc. As a result, when a unique date is selected, it
also selects the date granularity accordingly.
It also happens for date range - For
example, YTD, it is within the date granularity. YTM, it is within the month
granularity.
However, the date granularity is NOT always
explicitly illustrated. Indeed, granularity helps to make the analysis clearer.
When dealing with different granularities, date conversion is required, it can
be a burden to both human and the computer. . It is an important consideration
in the calendar design so that the confusion can be avoided.
Summary
After all the discussions and below
summarizes all the considerations:
·
Date granularity is an
important consideration, and conversion is required when dealing with different
levels.
·
Date is with hierarchy and date
conversion only happens in the hierarchy.
·
Human date range representation
is more user-friendly.
·
Computer, on the contrary,
requires a standard date representation.
·
All the date range
representation requires calculations to find out all the relevant dates, and it
is human unfriendly.
·
Sequence is useful for date
calculation.
·
Date integrity plays an
important role in the design.
·
Date uniqueness and data
granularity has a strong integrity and relationship.
·
Week is very special and has
two models including complete week model and incomplete week model.
·
It is best to pre-calculate the
date scenarios instead of calculating on the fly. Also, it is a must to confirm
the date model for all the expected behavior.
Previous Section <== 006 - Analytical Calendar - Date Conversion and Calculation