Technical Discussion | Analytical Calendar The Story | The Index Page
Analytical Calendar
Minor Enhancements
There are three minor enhancements about the Analytical Calendar including:
1. N/A in the Comparison Calendar
2. N Year Comparison in Week
3. N Day Rolling in Month End
These three were based on some feedbacks and enlightened up the new design.
Let's take a look the issue one by one with a possible solution and discussion.
N/A in the Comparison Calendar
While using the Analytical Calendar, it strictly follows the green-while-grey design in Qlik, i.e. the associative model requires four selections to make the Analytical Calendar and Comparison Calendar to function including Date Perspective, Unique Date, Analysis Perspective and Comparison Perspective.
Unfortunately, in some of the analysis, it does only require the Analysis Calendar rather than the Comparison Calendar. There is no way in Comparison Calendar to bypass the comparison. Thus, the associated results would not be accurate due to the inclusion of the comparison calendar. No matter one or many of the Comparison Perspective are selected, the association is broken in some sense.
As illustrated below, if nothing is selected, it includes all dates but even if one of the value is selected, it still has redundant dates in the comparison calendar that makes the association is not accurately associated with the required dates.
In order to fix this issue, "N/A" is introduced in the Comparison Perspective to allow association effective only for Analysis Calendar. "N/A" means no effect in Comparison Calendar. Thus, what is selected in Analysis Calendar, it directly reflects in the association. An example is illustrated below.
In general, whenever there is no Comparison Calendar requirement in certain sheets, it could simply lock the "N/A" value in the Comparison Perspective and once it exits the sheet, it can unlock the selection. Although it looks redundant to select "N/A", it is still clear to have what you select is what you see.
N Year Comparison in Week
In 2023, very lucky, 1 Jan starts on Sunday. Thus, in a full week design calendar, it has a total of 53 weeks. For all other years that 1 Jan is non-Sunday, it has only 52 weeks. For year with 365 days, it shifts for the start to the next weekday in next year. So, in 2024, 1 Jan starts on Monday. However, there are leap years having 366 days. This, therefore, creates an irregular shift pattern. Let's take a look the year from 1993 to 2024. Below table illustrates the weekday of 1 Jan and also it is a leap year.
Let's then take a look the comparison. As illustrated below, whenever it is passing through the week of 1 Jan on Sunday, a year originally compares 52 weeks before should shift 1 more week.
When talking about rolling, the comparison period should have the same number of days against the analysis period. Now, let's take an example of 2024-02-29 rolling 5 days. So, the date range would be from 2024-02-25 to 2024-02-29 (5 days). If it is comparing 1 year before, what should it be?
If using the start and end dates with 1 year back, it becomes 2023-02-25 to 2023-02-28 (4 days). It does not make sense for the rolling and comparison concept to have same number of days in the rolling periods.
Let's apply similar method like we define the date range of 2024-02-29 rolling 5 days. It is based on the end date to find 5 days before. So, in comparison, we first define the end date, i.e. 2023-02-28 (1 year before 2024-02-29, more precisely, it is 2023-02-29 by deducting 1 in the year component. Since, this date does not exists, it shifts to the nearest end of the month, i.e. 2023-02-28). With the end date at 2023-02-28, rolling 5 dates, it has the range between 2023-02-24 to 2023-02-28 (5 days).
With the same concept, 2024-03-31 rolling 3 days compare to 1 month before. The analysis period is from 2024-03-29 to 2024-03-31 (3 days) while the comparison period is from 2024-02-26 to 2024-02-28 (3 days). Does this make sense to you as well?
Conclusion
Analytical Calendar has many discussion how to manipulate the complicated date. The irregular grouping of Month, Week , Year makes the operations very difficult. The innovation of the Analytical Calendar aims at reducing the effort for human interpretation of these operations and try standardize the method of calculations. There is still a long way to make all these standardized and have a mutual agreement the usage and calculations in the world but I hope the shared concepts and implementation methods can continue to evolve. One day, the analysis can become more straight-forward, easy to learn and without confusion.
Thank you for reading. I hope it help you. Just leave a message if you have any discussion/share want to make.