Analytical Calendar
Technical Discussion
Analytical Calendar is extremely useful to be used in interactive dashboard. I have shared a number of articles related to this topic and hope that everyone can understand the ease and the difficulty when dealing with date and calendar. Different people might have different perceptions on how to use date and calendar. It is impossible to change the mind if it is already becoming a habit or get used into the same regular actions reacting daily. However, it is worth that you can try and understand further of what Analytical Calendar is and how it can make a difference in your analytic dashboards.
This article focuses on the technical design of analytical calendar and let you understand how it can make your life easier from the development and also from your daily use in analytics. One of the nice stuff is that the date associations are already built inside the Qlik associative data model. For developer, you don't need to work out a lot of set analysis to come up with the correct period. For analyst, you don't need to work have a lot of charts developed and the perspective for period is simplified into analysis and comparison periods.
If you are not familiar with Qlik associative data model, please try to get some understanding in the Qlik website first. Also if you have forgotten how to use the Analytical Calendar, please review in the previous post. Or if you have interested in how the analytical calendar comes up, you can click the link at the top to follow the story.
Analytical Calendar Data Model
First, let's look at the data model of the Analytical Calendar.
Analytical Calendar consists of five core tables. As shown in the above data model diagram, they include:
1. ANALYSIS_CALENDAR
This is the analysis calendar. It controls the selection of "Date Perspective", "Unique Date" and "Analysis Perspective". The main purpose of this table is to allow selection of analysis period.
2. %CUSTOM_LINKEDTABLE
It is a linked table to associate the ANALYSIS_CALENDAR to the PERSPECTIVE_CONTROL_CALENDAR. This table exists because of the need of Custom Unique Date selection as a comparison. Each analysis period, in fact, is associating a number of available comparisons.
3. PERSPECTIVE_CONTROL_CALENDAR
It controls the "Comparison Perspective" selection and it is also used to associate to the date range required in %DATE_RANGE_LINKEDTABLE. Each row is associating to a corresponding date range (i.e. %DATE_RANGE_KEY). This is a very important table because it has all date and calendar information, i.e. ANALYSIS, COMPARISON, CUSTOM.
4. %DATE_RANGE_LINKEDTABLE
It is associated a date range to the dates in the range.
5. DIM_CALENDAR
It is the dimension table of calendar to facilitate all the chart requirements on date and calendar. It has all combination of date components and the corresponding sequence.
There is a TRANSCATION table that is a dummy fact table for demonstration purpose only.
More on the Technical Design
There are a couple of "magic" lightened up the Analytical Calendar.
Analysis is a subset of Comparison
If you are small-hearted, you will notice the relationship between analysis and comparison in Analytical Calendar . To do analysis, you need to select
1. Date Perspective
2. Unique Date
3. Analysis Perspective
To do comparison calendar, you need an additionally Comparison Perspective. in other word, you must first have the analysis before you can do the comparison. So, there are tables ANALYSIS_CALENDAR, %CUSTOM_LINKEDTABLE and PERSPECTIVE_CONTROL_CALENDAR in the design to allow each analysis period to associate with all possible comparison periods, either comparison range or Custom Unique Date.
Taking an example of analysis period MONTH, 2023-Mar, YTM. It is associating with all comparison like 1 year before, 2 years before and so on, and Customer Unique Date like 2023-Jan, 2023-Feb, etc. This kind of association is built inside the data model.
From another point of view, in term of data-wise, it looks like analysis is a subset of comparison in terms of the required selections.
Generic Keys for Custom Unique Date Association
Interestingly, there is a table called %CUSTOM_LINKEDTABLE. Originally, the ANALYSIS_CALENDAR can directly associated to PERSPECTIVE_CONTROL_CALENDAR. In order to allow Custom Unique Date selections, this table is using the concept of generic key. Thus, all Custom Unique Date can be associated to any analysis period in ANALYSIS_CALENDAR.
If Custom Unique Date is not required, this table is apparently not required.
Magic %CONTROL_PERSPECTIVE
The %CONTROL_PERSPECTIVE is, indeed, the main control of which period to be applied. If no value is selected in this field, it means all analysis and all comparison. If "ANALYSIS" is selected, it means to enable analysis perspective but apparently, "Comparison Perspective" must be ignored, i.e. {<%CONTROL_PERSPECTIVE={'ANALYSIS'}, "Comparison Perspective"=>}. On the other hand, to enable comparison, it needs to ignore ANALYSIS, i.e. {<%CONTROL_PERSPECTIVE-={'ANALYSIS'}>}. Regarding to the selection in "Comparison Perspective", the corresponding date ranges are already associated.
You can think of a concatenation of Analysis Calendar, Comparison Calendar and Custom Calendar all into this table. And %CONTROL_PERSPECTIVE is to control which one to be used.
This is the crucial part why the developer can have a very simple set analysis to control all kinds of date intelligent selections for analysis and comparison.
The use of % Fields and Tables
You might notice there are a lot of % fields. All will be hidden after SET HidePrefix = '%' is applied. The purpose is to make sure the end users would not be able to search and find the % fields and also the tables. They are just for internal control of the Analytical Calendar and should not confuse the analysis or the experience in using the interactive dashboard. Apparently, if you are very familiar with these, you can still let it show up in the interactive dashboard.
The Calendar Summary Text Box
The text box is an very important element in the overall design. In terms of selection, human normally prefers to use date range representation while in terms of verification, it prefers a range of from and to date to verify. With this principle, the textbox is not only showing both the selected values of analysis or comparison but also, the actual date range of the selected periods in analysis and comparison. It is absolutely clear of what you select is what you get.
Because of the flexibility of Analysis Calendar, it also allows select discrete dates. For analysis and comparison selection, there is no problem to show all selected values. The only concern is the space available to show all. But for the range, it is impossible to represent. It, thus, flags up a keyword * Discrete to indicate the date range is not continuous. However, this kind of usage is rare.
Conclusion
The design of the Analytical Calendar is a generic. It does not limit to just include YTD, MTD, rolling, etc in analysis and comparison range in comparison. There are many other possibility, e.g. Today, This Month, Last Month, Last Year, etc. All kinds of date representation is able to be incorporated into the Analysis Perspective and Comparison Perspective. Or there might be other kinds of usage that is not yet discovered. As a short summary, it looks like date and calendar is simple but when you are dealing with them, you will find the mystery of it. And throughout all the articles and discussions, I hope you all can re-think the best usage of it and not just stay in the traditional way. The digital world needs a lot of new input and innovation to move forward. Tiny things does not mean nothing but it is true that small things can also change the world.
Even though, the Analytical Calendar has been evolved for so many times, definitely, there are still things that can be improved. If you have comments or ideas, please kindly leave a message below to discuss and let's see if it can come true for benefiting the digital world.
Thank you for reading. I hope you enjoy the details shared.
No comments:
Post a Comment