Analytical Calendar The Story | The Index Page
2023-05-15
Analytical Calendar
Analytical Calendar Design
Analytical calendar consists of three
calendars including Analysis Calendar, Comparison Calendar and Custom Calendar.
Analysis Calendar is responsible for reflecting the date selection for analysis.
Comparison Calendar is responsible for reflecting the comparison selection for
date comparison. Custom Calendar is responsible for custom date range
selection.
Analytical Calendar can deal with actual
dates like year, quarter, month, week, date, and also allow standard date range
like YTD, MTD, YTM, etc, and even more for rolling in different date levels
like year, month, week, date. These date range representations are simply converted
into the range bounded by start date and end date.
Comparison Calendar references the
Analytical Calendar and, on top of it, apply comparison unit and range for date
comparison. Integrity is enforced for the comparison unit and comparison range.
The Custom Calendar can accommodate any
selected date range and allow comparison selection of any date range.
Basic Elements
There are a few basic elements in the
calendars.
1.
Date Range Type
It
has two components including the date granularity and the date range type. For
example, D-Actual, D-YTD, M-Actual, M-YTM, etc. The date granularity is to
control which unique date to show, and the date range type is to control which
date range it means for the unique date selected.
2.
Unique Date
It is a combination of all the unique dates
including year, year quarter, year month, week year week and date. Once the
date range type (i.e. the granularity) is selected, it will show the relevant
unique date of the same granularity.
3.
Rolling Range
It
works with the date range type with rolling such Y-Rolling, Q-Rolling,
M-Rolling, W-Rolling and D-Rolling. It specifies the rolling range required
starting from 1 to the maximum allowed rolling period. If non-rolling date
range type is selected, it shows N/A.
4.
Comparison Unit
The
comparison unit includes Y, Q, M, W and D. It means the date unit for the
comparison, i.e. year before, quarter before, month before and so on.
5.
Comparison Range
The
comparison range means how many date units for the comparison period. It starts
from 1 to a maximum allowed comparison range.
It
is a list of dates for handy picking up the range for comparison.
Analysis Calendar
Analysis calendar consists of the below
elements:
1.
Date Range Type
It
specifies the date granularity and also the date range type, e.g. D-Actual,
D-YTD, M-Actual, etc. There are plenty of cases can be defined based on the
need.
2.
Unique Date
It
is specifying the date using a unique date format by the date granularity.
·
Date Level => Year-Month-Day,
e.g. 2022-02-02.
·
Quarter Level =>
Year-Quarter, e.g. 2022-Q1
·
Month => Year-Month, e.g.
2022-02.
·
Week => Year-Week, e.g.
2022-W20.
·
Year => Year, e.g. 2022.
It is a unique date representation based on
its date granularity. In other words, the Date Range Type controls the
corresponding unique dates in the same date granularity level, e.g. Y-Actual,
it shows 2022, 2021, etc. while M-Actual, it shows 2022-01, 2022-02, etc. As a
result, there is no ambiguity on what date granularity you are selecting. For instance, M-Actual for 2022-02 is
selecting a month, D-Actual for 2022-02-01 to 2022-02-28 is selecting 28 dates.
3.
Rolling Range
It
is specifying the number for rolling period with respect to the date
granularity, e.g. N/A (it is not rolling related), 1, 2, 3, etc. If date
granularity is Y, it means rolling by year, if M, it means rolling by month, so
far and so on.
As
a result, based on selecting these three attributes, it is allowed to select
from one particular date to a range of dates. Date range includes standard date
range like YTD, MTD, YTM, etc and rolling N years, N months, N weeks or N days.
Comparison Calendar
Comparison calendar consists of the below
elements:
1.
Date Range Type
It
is the same as the date range type in the Analysis Calendar as the comparison
reference.
2.
Unique Date
It
is the same as the Unique Date in the Analysis Calendar as the comparison
reference.
3.
Rolling Range
It
is the same as the Rolling Range in the Analysis Calendar as the comparison
reference.
4.
Comparison Unit
It
specifies which date level comparison is required. It has a special comparison
unit called custom. This means to pick up custom dates.
·
Year Level, i.e. Y
·
Quarter Level, i.e. Q
·
Month Level, i.e. M
·
Week Level, i.e. W
·
Date Level, i.e. D
·
Custom Level, i.e. Custom
The available comparison unit is enforced
with integrity. Once the date range type (date granularity) is selected, only
relevant comparison unit is shown. For example, 2022, it is unable to compare
in month level. Instead, it only allows Year level comparison.
The
two comparison methods, i.e. comparison unit and custom date range are mutually
exclusive to each other. In other words, if comparison unit method is used,
custom date range comparison is deactivated while using custom date range
comparison, comparison unit is deactivated.
5.
Comparison Range
It
specifies the date range for comparison regarding to the available comparison
units. Custom level does not have a comparison range.
Custom Calendar
There is an additional calendar called the
custom calendar. It is the same as a master calendar. It allows to select
exactly which dates as the comparison period.
Integrity Control
Integrity control is one of the core features
in the analytical calendar because a calendar is normally very flexible for
users to do selection. However, if all the date integrity is manipulated by
users, it is instead not user friendly.
Analytical Calendar, thus, pre-builds all
the integrity control. In other words, the user would not be possible to select
something that will be confused. Technically, it reduces the effort for the
developer to “fight” against the combinations and scenarios for the usage. Complicated
calculations (like the date conversion) would not be required anymore in the
dashboard development. Therefore, developer can purely focus on the front-end
visualization.
Below shows some examples of considerations of the data integrity about date. When the corresponding date range type is selected, it only allows the corresponding comparison unit.
|
Comparison Date Range Type |
Comparison Unit |
|
D-Actual |
D |
|
D-Actual |
M |
|
D-Actual |
W |
|
D-Actual |
Y |
|
D-MTD |
M |
|
D-MTD |
Y |
|
D-Rolling |
D |
|
D-Rolling |
M |
|
D-Rolling |
W |
|
D-Rolling |
Y |
|
D-YTD |
Y |
|
M-Actual |
M |
|
M-Actual |
Y |
|
M-Rolling |
M |
|
M-Rolling |
Y |
|
M-YTM |
Y |
|
Y-Actual |
Y |
|
Y-Rolling |
Y |
|
Q-Actual |
Y |
|
Q-Actual |
Q |
|
Q-Rolling |
Y |
|
Q-Rolling |
Q |
|
Q-YTQ |
Y |
|
W-Actual |
W |
|
W-Actual |
Y |
|
W-Rolling |
W |
|
W-Rolling |
Y |
|
W-YTW |
Y |
Taking an example YTD, it makes sense only
to compare last year instead of yesterday. As a result, if YTD is picked, the only
comparison unit allowed is year. Another example like rolling 1 year, it makes
more sense to compare by year instead of month, week or day. As a result, year
is the only selection in the comparison unit for rolling year.
It will be good to support all kinds of
rolling and comparison. However, it also generates a lot of data when building
up the calendars. Therefore, certain limits are set to avoid overwhelming
usage.
For the rolling range, by default, it is configured as below.
|
Date Range Type |
Rolling Range |
Reason |
|
D-Rolling |
30 |
Allow 30 days rolling, which is less than the
number of days in a month. If 1 month rolling is required, then select 1
month instead. |
|
M-Rolling |
11 |
Allow 11
months rolling, which is less than number of months in a year. If 12 months
rolling is required, then select 1 year using ‘Y-Actual’ in Unique Date
instead. |
|
W-Rolling |
26 |
Allow 26 weeks rolling, which is less than
the number of weeks in half year. If half year rolling is required, then
select 6 months using ‘M-Actual’ in Unique Date instead. |
|
Q-Rolling |
3 |
Allow 3
quarters rolling, which is less than number of quarters in a year. If 4
quarters rolling is required, then select 1 year using ‘Y-Actual’ in Unique
Date instead. |
|
Y-Rolling |
3 |
Depends on data size |
For the comparison range, by default, it is
configured as below.
|
Comparison Unit |
Comparison Range |
Reason |
|
Y |
3 |
Depends on data size. |
|
Q |
3 |
Allow 3
quarters comparison, which is less than number of quarters in a year. If 4
quarters comparison is required, then select 1 year using ‘Y-Actual’ in
Unique Date instead. |
|
M |
11 |
Allow 11 months comparison, which is less than
number of months in a year. If 12 months comparison is required, then select
1 year using ‘Y-Actual’ in Unique Date instead. |
|
W |
26 |
Allow
26 weeks comparison, which is less than the number of weeks in half
year. If half year comparison is required, then select 6 months using
‘M-Actual’ in Unique Date instead. |
|
D |
30 |
Allow 30 days comparison, which is less than the
number of days in a month. If 1 month comparison is required, then select 1
month instead. |
The date is now represented using Date
Range Type, Unique Date, Rolling Range, Comparison Unit, Comparison Range and
Custom Date. Consider the following example:
Date Range Type =
D-YTD
Unique Date=
2022-02-05
Rolling Range = N/A
Comparison Unit = Y
Comparison Range = 1
The analysis
calendar should reflect date from 2022-01-01 to 2022-02-05 and comparison
calendar should reflect date from 2021-01-01 to 2021-02-05. There is an
association relationship of the representation to the actual date selection. It
can be imagined that this will generate a huge number of records. To minimize
the number of records, a new representation is introduced.
If it is
represented using date, the analysis calendar requires 36 date values for this
association and the comparison calendar also requires 36 date values for the
association. In total 72 records are required. However, a better representation
would be 2022-02-05|35. It means 0 to 35 days before 2022-02-05, i.e.
2022-01-01 to 2022-02-05. In this representation it only requires 2 records. It
greatly reduces the number of records.
And more
importantly this representation can be used. For example,
Date Range Type =
D-Rolling
Unique Date=
2022-02-05
Rolling Range = 36
Comparison Unit = Y
Comparison Range = 1
It is
the same to have 2022-02-05|35 and 2021-02-05|35.
Previous Section <== 007 - Analytical Calendar - Calendar Consideration and Standardization
Next Section ==> 009 - Analytical Calendar - Analytical Calendar Implementation