Qlik Sense - Analytical Calendar

[Kongson]

Update 2023-06-20

I have summarized the details of the analytical calendar into my personal blog.  You can find more information in https://kongsoncheung.blogspot.com/2023/05/analytical-calendar-story.html

First Post Starts

Hello everyone.  This is my first post in the Qlik community.  I start to write the posts because I would like to share the ideas on what I have done and experienced before. Might be there is someone who already applied similar solutions but I think it is good to share and keep improving with everyone in the community.  Let's evolve.

Topic

I would like to share an idea about using calendar in Qlik Sense.  Probably, experienced users are already made use of many master calendars, auto calendars for the dashboard development.  However,  when manipulating with dates, it usually turns out to have a lot of complicated set analysis and it is very difficult to maintain, e.g. year to date (YTD), year on year (YoY), month on month (MoM), week on week (WoW), etc.  A date range is always required to be calculated based on the current date selection and date sequences are in place for calculation, e.g. year sequence, year month sequence, year week sequence, etc.  By getting the differences between the sequences, the date content can be controlled as desired.  And more difficult is that when a custom date range comparison is required, it usually requires to have alternate state in place.

To resolve this, it is introduced the Analytical Calendar.  It is pre-calculating all the date range scenarios and put them into associations.  The performance is not only just better because of the powerful Qlik associative engine but also a better advantage for the developer is to create a consistent and coherent design across the dashboard and fully integrate to the “green-white-grey” user experience.

Analytical Calendar

Analytical Calendar consists of two calendars including As-Of Calendar and the Comparison Calendar.  As-Of Calendar is responsible to reflect the date selection while Comparison Calendar is responsible to reflect the date selection and comparison selection for date comparison.

Analytical Calendar can deal with actual dates like year, 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 can deal with two different kinds of comparison including first by unit and range (e.g. N year/month/week/day before), and second by custom date range (it does not require to use any alternate state). 

The analytical calendar also manages to resolve the issue about the date granularity.   For example, in normal calendar if you select Feb-01 to Feb-28, are you selecting a month or 28 dates?  And if it is then to compare 1 month before, should it take Jan-01 to Jan-31 (month comparison) or Jan-01 to Jan-28 (day comparison)?

Below is an illustration about the Analytical Calendar.

Keep Connected

I am unable to share the QVF example at the moment but if you need more information, kindly drop me a message for further discussion.  Meanwhile, I will continue to share more stuff in the new posts. 

[vikasmahajan]

Thanks for sharing really it is interesting i am also looking the same solution : can you please send me demo qvf to vikasomahajan@gmail.com

Vikas 

[Kongson]

Sorry that due to some reasons, I am unable share the demo qvf.   You may refer to the below simplified data model diagram  to understand further.

[vikasmahajan]

can you please paste script much appreciated

Thanks

Vikas 

[Kongson]

As said, so sorry that I am unable to share the details.

The concept is quite straight forward to separate Year, Year Month, Year Week and Date and generate the date range association for each of the date range type like YTD, MTD, YTM, etc.  Once it is ready, then to further generate the comparison like compare unit = Y and compare range = 1 depending on the need of the application.

I hope this helps.