Analytical Calendar - Qlik Associative Model Generation

Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2025-12-20

Analytical Calendar

Qlik Associative Model Generation

Thanks for following up all the posts.   I am eventually confident to release the code for public use.  Please leave your feedback and use it happily and evolve analytics.  The code is for Qlik to make best of the usage of associative data model to maximise the usage of the analytical calendar.

How to Configure the Analytical Calendar

First, you can customise the code by yourself but make sure you understand well before doing so.  The easiest way is to import the analytical_calendar.qvs into the Qlik script and start the configuration.  What it needs two major steps:

1.  Change the parameter.

vStartDate

It is the start date of the calendar.

vEndDate

It is the end date of the calendar.

vMaxYRolling

It is the maximum rolling years.

vMaxQRolling

It is the maximum rolling year quarters.

vMaxMRolling

It is the maximum rolling year months.

vMaxWRolling

It is the maximum rolling year weeks.

vMaxDRolling

It is the maximum rolling days.

vMaxYComparison

It is the maximum comparison year range.

vMaxQComparison

It is the maximum comparison year quarter range.

vMaxMComparison

It is the maximum comparison year month range.

vMaxWComparison

It is the maximum comparison year week range.

vMaxDComparison

It is the maximum comparison date range.

2.  To pick one of the week usage, either Incomplete Week (Broken Week) or Complete Week (Unbroken Week).

Just remove the one that you do not want to use.

Associate to the Analytical Calendar and How to Write the Expression

Just load them into your dashboard and make sure you have a fact table with %DATE_KEY to associate to the analytical calendar.

In general, you will just need two similar expressions:

1. analysis period, sum({<[Comparison Perspective]={'N/A'}>} Exp1)

2. comparison period, sum(Exp1)

I keep this one very short.  In case if you have any questions, feel free to jot me a note.

Same details, you can find in my github.  Thank you!

Thank you for reading.  I hope you enjoy using the analytical calendar.  Just leave a message if you have any discussion/share want to make.

* If you like my post, support me buy me coffee https://buymeacoffee.com/kongsoncheung.

Analytical Calendar - Public Release for 2022 to 2026

   Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2025-12-18

Analytical Calendar

Public Release

2022 to 2026

Analytical Calendar is continuing its journey with many versions developed with different forms and attempts that aims at reducing the development effort and boost the overall experience in data analytics.   Each version has its own pros and cons but after a lot of effort, I am now trying to release a public version that I think that is easy enough for everybody.

Analytical Calendar - Public Release for 2022 to 2026

The QVD files can be found in:

1. ANALYTICAL CALENDAR

2. DIM_CALENDAR

3. LINKED_TABLE_DATE_RANGE

Just load them into your dashboard and make sure you have a fact table with %DATE_KEY to associate to the analytical calendar.

In general, you will just need two similar expressions:

1. analysis period, sum({<[Comparison Perspective]={'N/A'}>} Exp1)

2. comparison period, sum(Exp1)

Analytical Calendar Review

Instead of selecting year, month or day, analytical calendar requires you to select three basic fields including:

1. Analysis Perspective

i.e. in what time analysis you want to perform, e.g. year? Year month? Date? YTD? YTM? rolling?

2. Unique Date

i.e. Which date? e.g. 2025 (for Year granularity), 2025-Q1 (for quarter granularity), etc.

3. Comparison Perspective

i.e. the comparison regarding to the analysis period, e.g. 1 Year before, 2 months before, etc.

Look like not user friendly?  Yes, it is just a habit stuff.  Once you start using it, you can start to understand that the traditional calendar requires you a lot of effort to manipulate for a valid combination.  The analytical calendar has incorporated all dependencies and integrated well.  You would not be able to make time analysis in a strange way, neither in selection nor your brain processing.

Analytical Calendar Configuration

It covers 5 years of data with period from 2022-01-01 to 2026-12-31 with 5 level of granularity including Year, Year Quarter, Year Month, Year Week and Date.

And it has the following analysis perspective and comparison perspective:

• Analysis Perspective
• Year
• Rolling Year (2 - 3 years)
• Quarter
• Year to Quarter
• Rolling Quarter (2 - 3 quarters)
• Month
• Year to Month
• Rolling Month (2 - 11 months)
• Week
• Year to Week
• Rolling Week (2 - 26 weeks)
• Date
• Year to Date
• Month to Date
• Rolling Day (2 - 30 days)
• Comparison Perspective
• Comparison Year Before (1 - 3 years)
• Comparison Quarter Before (1 - 3 quarters)
• Comparison Month Before (1 - 11 months)
• Comparison Week Before (1 - 26 weeks)
• Comparison Day Before (1 - 30 days)
• Comparison - N/A

* Week is complete week (unbroken week).

Thank you for reading.  I hope you enjoy using the analytical calendar.  Just leave a message if you have any discussion/share want to make.

* If you like my post, support me buy me coffee https://buymeacoffee.com/kongsoncheung.