Technical Discussion | Analytical Calendar The Story | The Index Page
Analytical Calendar
Concept In Brief
Analytical Calendar looks like simple but also complicated. To make sure it is easier to digest and understand, I would like to provide the concept in brief.
Analytical Calendar consists of 4 core fields including:
Date Perspective
It is to define what date granularity is required for the analysis and comparison. Without this selection, it is still possible but the Unique Date selection is difficult to filter out the required Unique Date in the list.
Unique Date
Based on the date perspective, it allows the corresponding date to be shown for selection. For instance, If Year is selected in Date Perspective, Unique Date will show 2024, 2023, 2022, etc; if Month is selected, it shows 2024-Feb, 2024-Jan, 2023-Dec, etc.
Analysis Perspective
With the Unique Date as reference, Analysis Perspective define the period requirements such as:
- Actual
- YTD, YTM, YTW, etc
- Rolling N Year/Quarter/Month/Week/Day
Comparison Perspective
With the defined Analysis requirements, it further selects the comparison period with respect to the analysis requirements.
- N Year/Quarter/Month/Week/Day Before
- Custom Year/Quarter/Month/Week/Day
- N/A
How the Calendars Work
In Analytical Calendar, the usage is like below flow.
Analysis Period --> Comparison Period --> Calendar Control --> Fact Table Association
Analysis Period
The Analysis Period is purely defined by Date Perspective, Unique Date and Analysis Perspective.
Date Perspective and Unique Date are apparently a new method to define a date in particular granularity. This method eliminates conflicts in human interpretation and manipulation about the selected date by date hierarchy, i.e. Year-->Month/Week-->Date.
Once the Date with Granularity is defined, next is to define the date range by Date Perspective. The selected Unique Date is actually the date end of the date range. If date range is having same start and end date, it is called Actual. Other will be represented by YTD, YTM, Rolling N Year/Month/Quarter/Week/Day.
All these then construct the Analysis Calendar.
Comparison Period
Comparison Period cannot exist without the Analysis Period. It is, in fact, a period with reference to the date range of the analysis period. The Comparison Perspective is generalized with a standard representation in two ways. One is to define a date range before and the other is free date selection.
There is one very special scenario for Comparison Perspective is no comparison period. For completeness purpose, it is required for a value to be selected in the comparison period.
As a result, three categories are available. And the Date Perspective, Unique Date and Analysis Perspective and Comparison Perspective construct the Comparison Calendar.
Calendar Control
Both analysis period and comparison period are, in fact, trying to reflect the selection in Analysis Calendar and Comparison Calendar. The analysis period defined is tagged as ANALYSIS while the comparison period defined is tagged as COMPARISON. If the CONTROL is not selected with a value, it means both analysis and comparison.
This CONTROL is very important in order to control which calendar to take effect.
Fact Table Associated
The Fact Table associated with both analysis and comparison periods in the form of the actual date association. Based on the Control (ANALYSIS or COMPARISON), it allows to reflect the analysis period and comparison period.
Two examples are illustrated below.
The second example is similar to the first example but this time, no comparison period is defined. Thus, the Calendar Control only has ANALYSIS Period and Fact Table is only reflecting the date range from 2023-05-01 to 2023-05-31.
Summary
Analysis Calendar provide methods to select values to define the ANALYSIS period while Comparison Calendar provide methods to select values to define the COMPARISON period. Fortunately, both calendars share the same fields including Date Perspective, Unique Date and Analysis Perspective. This is one of the main design of Analytical Calendar and it is one reason why it can simplify the selections. The other main concept is the Calendar Control. It is important that there is a way to distinguish the ANALYSIS and COMPARISON periods. Incorporating these concepts altogether, it is the Analytical Calendar.
I hope this brief description can help you understand further about the Analytical Calendar. Next, I would share how to apply Analytical Calendar into other tools like PowerBI and Tableau.
Thank you for reading. I hope it help you. Just leave a message if you have any discussion/share want to make.
No comments:
Post a Comment