Sunday, May 14, 2023

Analytical Calendar - 007 - Calendar Considerations and Standardization

Post Index

 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

 

Analytical Calendar The Story | The Index Page