Showing posts with label As of Calendar. Show all posts

Wednesday, December 17, 2025

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.

Saturday, November 30, 2024

Analytical Calendar - Feeling and Experiencing for Feedback

Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2024-10-30

Analytical Calendar

Feeling and Experiencing for Feedback

QVD Sharing for 2024 and 2025

Analytical Calendar has been developing for quite some time. And there are quite a number of enhancements evolved in the design and also improved algorithms to tackle all the date manipulation issues. With feedback on developers and users, it evolves the way of doing analysis with the new ways of using calendar.

Today, I am going to share a couple of QVD files and also the Qlik Sense template application such that you are able to feel and experience the benefits of analytical calendar. I have put the files into google drive to share and you are easily to use web file data connection to grab and use them directly or simply download into your drive and try it out.

Analytical Calendar Date and Month Level

There are three analytical calendars are going to share:

Date Level - Complete Week
Date Level - Incomplete Week
Month Level

* The concept of complete and incomplete week, please refer to the previous post.

For date level, it covers 5 years of data with period from 2021-01-01 to 2025-12-31. And it has the following analysis perspective and comparison perspective:

Analysis Perspective

Actual Year
Rolling Year (2 - 3 years)
Actual Quarter
Year to Quarter
Rolling Quarter (2 - 3 quarters)
Actual Month
Year to Month
Rolling Month (2 - 11 months)
Actual Week
Year to Week
Rolling Week (2 - 26 weeks)
Actual 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

For month level, it covers 5 years of data with period from 2021-Jan to 2025-Dec. And it has the following analysis perspective and comparison perspective:

Analysis Perspective

Actual Year
Rolling Year (2 - 3 years)
Actual Quarter
Year to Quarter
Rolling Quarter (2 - 3 quarters)
Actual Month
Year to Month
Rolling Month (2 - 11 months)

Comparison Perspective

Comparison Year Before (1 - 3 years)
Comparison Quarter Before (1 - 3 quarters)
Comparison Month Before (1 - 11 months)
Comparison - N/A

Analytical Calendar QVD Files

The following are the shared files:

Date Level - Complete Week

Date Level - Incomplete Week

Month Level

Analytical Calendar Qlik Sense Template

The three Qlik Sense templates are shared in my github.

How to use Web File

The Qlik Sense template is using the web file to directly load the calendar from my google drive. Below is a brief guide on the step required.

1. First, in data load editor, right hand side, select "Create New Connection".

2. Then, select "Web file" under "Data Sources".

3. Then, enter the QVD URL (the QVD file mentioned above) and also a name for the data connection.

4. Then, a data connection is created. Select the table to "select data".

5. Then, it will load the data from the URL to preview. And then select "Insert Script"

6. Then, the required script to load the data will be added.

7. Use the same steps for the two QVDs.

e.g.

AnalyticalCalendarMonthLevel_2025.qvd
DimCalendarMonthLevel_2025.qvd

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.

Sunday, October 20, 2024

Analytical Calendar - New Update 2024-10-20

Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2024-10-19

Analytical Calendar

New Update

A simplified version with enhanced feature

- Simplify to two tables

- Introduce comparison group

Analytical Calendar has been enhancing and it hopes to help analytics becoming simpler, more standard and allowing both developers and analytic users to take advantages over it for its generic and harmony design of using date for analysis and comparison.

For analytic users, it does not require to bear the burden of date manipulation because the selection and the visualization are already integrated very well together to bring along the effect of what you select is what you get. Although, it needs to first understand the concept, it is fairly easy to ride on.

For developers, it does not require to write a number of expressions for different date requirements and avoids creating plenty of similar charts to achieve different date granularity to make overall design neat and focused. The development is shortened so that more effort can be put into the visualization for best analytic purposes.

Today, this article focuses on the description of the new version of analytical calendar on two areas:

1. two tables design

2. comparison group is added

Two Tables Design Analytical Calendar

Analytical calendar has been enhancing along the time. In the previous version, it requires 3 core fields to control over the analysis period and comparison period that includes:

Analysis Perspective
Unique Date
Comparison Perspective

These three fields intelligently help you deal with all complicated calculations in the backend and avoid confusion when dealing with impossible date component combinations.

In the previous design, it has three core tables:

Analytical Calendar (ANALYTICAL_CALENDAR)
Date Range Linked Table (DATE_RANGE_LINKED_TABLE)
Date Dimension (DIM_CALENDAR)

In majority of the time, a number of people might not be familiar with the operation of linked table. Thus, the latest version combines the linked table and analytic calendar into a single analytical calendar table as below:

The disadvantage is that the ANALYTICAL_CALENDAR will become larger with more rows but it is still manageable with modern server resources. The advantage is that is does not required to manage the linked table which is a bit difficult to understand without technical knowledge.

With this model design, the functionality and everything remains the same.

Comparison Group

If your eyes are good enough, you might notice a new field "Comparison Group" is introduced in the ANALYTICAL_CALENDAR table. It is a new function for comparison visualization. To understand it, let use an example. For instance, the selection of the 3 core fields are:

Analysis Perspective (Rolling 3 months)
Unique Date (2024-Mar)
Comparison Perspective (1 Quarter before)

For KPI, it is easy to compare the total of 2024-Jan to 2024-Mar and 2023-Oct to 2023-Dec. If it wants to break down into detail to compare each individual period inside the period, it might not be possible . The comparison group is, thus, created to group these comparison requirements into groups. Below illustrate an example.

The analysis period 2024-Jan to 2024-Mar has three individual year month including 2024-Jan, 2024-Feb and 2024-Mar while the comparison period has individual year month including 2023-Oct, 2023-Nov and 2023-Dec. The comparison group helps to group these period together. As a result, you are able to compare in detail which year month is performing good or bad inside the analysis and comparison period.

The comparison group is still exploring for more visualization capability. Apparently, it is very useful for a bar chart to compare across the periods to identify which works great and which works badly.

Thank you for reading. I hope it help you. 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.

Saturday, May 25, 2024

Tableau - Analytical Calendar Attempt

Post Index

Analytical Calendar The Story

2024-05-25

Analytical Calendar

Applying in Tableau

How to implement in Tableau

This post is to provide you an idea how to implement Analytical Calendar in Tableau. Or more directly, it is how I try to put the Analytical Calendar into Tableau for an attempt so that Tableau can also enjoy the simplicity of the analysis and comparison periods as well as the simplified selections to navigate with the time.

In order to implement Analytical Calendar is not straightforward. It cannot direct plug in and use. And a few tuning is required and it also requires to understand deeper of Tableau.

In the following, I will share what I have encountered and what I have done. Let's start!

Differences in Tableau

First of all, the design of tableau is quite different from Power BI and Qlik. The major difference is that it is query-based. What it means is that any data required from the data sources, it triggers a query. It uses joins regarding to the data model. No matter it is a filter, a measure calculation, etc, it has its own mechanism to get data from the data sources. Even if it is using Extract, it is still query-based.

This means for Analytical Calendar, it requires a way to facilitate a fast way to retrieve data such that it can reduce the time and resource require to link up the required period data.

Another headache difference is that Tableau is unable to override filters. Once it has been configured in the worksheet, it does not have any methods to override the filter values. Tableau has a hierarchy of filtering from Extract, Data Source, Context, Dimension, Measure to Table as illustrated below.

https://www.thedataschool.co.uk/tristan-kelly/different-types-of-filters/

Although you might find LOD to help but it can only ignore dimension in the worksheet dataset. As a result, it is not freely for you to control "N/A" or the Comparison Perspective as what you want.

New Design to Adapt Tableau

With the limitations or the differences in Tableau, the Analytical Calendar design has to be updated and make a bit different compared to Power BI and Qlik in order to adapt to Tableau.

In the latest version of Analytical Calendar, only three elements are required to be selected including Analysis Perspective, Unique Date and Comparison Perspective and in measures, we did the trick to modify the Comparison Perspective to "N/A" in order to make an analysis period selection. However, Tableau isn't able to achieve the same.

In Tableau Analytical Calendar design, the three fields are remained but the selection of these three fields are not directly as what you selected. Instead, each pair of selection will associate together with both Analysis Period and Comparison Period. This is to overcome the filter limitation in Tableau. Such that, in the worksheet, you are able to control to show the Analysis Period or the Comparison Period by the additional field called Calendar Control.

The below diagram illustrated an example of the design:

In the example, You are looking for 2024-05-20 (Actual Date) to compare 1 day before. This selection, in the data model, is associating with two Date Range Key, i.e. Analysis Period and Comparison Period. In tableau measures, it can control to use Calendar Control = 'ANALYSIS' for analysis period while Calendar Control = 'COMPARISON; for comparison period. Although it increases the number of rows, because we are only a limited combinations of selection of the Analytical Calendar three core fields, it greatly narrows down the required rows in joining. Overall, only a little resource is required to compensate the Tableau design limitation.

Tableau Analytical Calendar Template

I have also put the Tableau template in https://github.com/kongson-cheung/analytical-calendar/blob/main/Tableau%20Analytical%20Calendar%20Examples/AnalyticalCalendar6.0_Tableau.twbx.

Please have a try and let me know your comment.

Below are the screen captures of the template. It does not have nice visual as the focus is to show the usage of Analytical Calendar.

The same layout is developed in Tableau similar to Qlik and Power BI.

The beauty of Analytical Calendar is that you are free to travel with time on analysis period and its comparison period. Single presentation can already satisfy YoY, MoM, YTD, etc.

Thank you for reading. I hope it help. Leave me message if you have any discussion or want to share your views.

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

Saturday, April 20, 2024

Analytical Calendar in Google Drive

Post Index

Analytical Calendar The Story

2024-04-20

Analytical Calendar in Google Drive

Data File Sharing and How to Use

Analytical Calendar (Default Mode) from 2015 to 2024

This post is to provide you a step by step guide on how to integrate the analytical calendar into your analytic dashboard. The analytical calendar files are now available in Google Drive (Please send me a request and I will share you the files).

The Analytical Calendar consists of the following information:

1. It has calendar information from 2015-01-01 to 2024-12-31.

2. It has date granularity of Year, Quarter, Month, Week and Date.

3. Week is using Complete Week concept.

4. Analysis Perspective include:

a) For Year

i) Actual Year

ii) Rolling 2, 3 Years

b) For Quarter

i) Actual Quarter

ii) Year to Quarter

iii) Rolling 2, 3 Quarters

c) For Month

i) Actual Month

ii) Year to Month

iii) Rolling 2 - 11 Months

d) For Week

i) Actual Week

ii) Year to Week

iii) Rolling 2 - 26 Weeks

e) For Date

i) Actual Date

ii) Year to Date

iii) Year to Month

iv) Rolling 2 to 30 Days

5. Unique Date include:

a) Year from 2015 to 2024

b) Quarter from 2015-Q1 to 2024-Q4

c) Month from 2015-Jan to 2024-Dec

d) Week from 2015-W2 to 2024-W53

e) Date from 2015-01-01 to 2024-12-31

6. Comparison Perspective include:

a) N/A

b) For Year

i) From 1 to 3 Years before

c) For Quarter

i) From 1 to 3 Years before

ii) From 1 to 3 Quarters before

d) For Month

i) From 1 to 3 Years before

ii) From 1 to 3 Quarters before

iii) From 1 to 11 Months before

e) For Week

i) From 1 to 3 Years before

ii) From 1 to 26 Weeks before

f) For Date

i) From 1 to 3 Years before

ii) From 1 to 3 Quarters before

iii) From 1 to 11 Months before

iv) From 1 to 26 Weeks before

v) From 1 to 30 Days before

7. It might have more than required calendar information and that can be filtered. It will be demonstrated how to do.

Step by Step Guide

In Qlik SaaS, create a new application or use the application that you would like to use the Analytical Calendar. And then go to Data Load Editor to begin.

1. Click "Create New Connection".

2. Click "Google Drive".

3. Click "Authenticate".

4. Enter the Gmail account.

5. Enter the password.

6. Click "Continue".

7. Click "Allow".

8. Click "Copy to Clipboard".

9. Paste the link and click "Verify".

10. Update the Name and click "Test Connection".

11. Click "Close".

12. Click "Create".

13. Click the folder icon under the newly created connection.

14. Select the Analytical Calendar file and click "Select".

15. Wait.

16. Do the same for Date Range Linked Table. Select the file and click "Select".

17. Do the same for Date Dimension. Select the file and click "Select".

18. Modify the script.

a) Add the Table Name.

b) In ANALYTICAL_CALENDAR, add the where clause

Where left([Unique Date], 4) >= 'YYYY' (YYYY is between 2015 to 2024)

c) In DATE_RANGE_LINKED_TABLE, add the where clause

Where exists(%DATE_RANGE_KEY)

and [%DATE_KEY] >= 'YYYY-MM-DD' (YYYY-MM-DD is between 2015-01-01 to 2024-12-31)

d) In DATE_DIMENSION, add the where clause

Where exists(%DATE_KEY)

All these will eliminate the unnecessary data.

19. Click "Load Data".

20. Enjoy using the Analytical Calendar !!

Conclusion

It is a no cost sharing. Very Simple and can let you enjoy the advantages of the Analytical Calendar. The settings are default and definitely can be customized for specific usage. If there is a demand, it would later enhance the overall distribution method to really help contribute to the Analytics realm. For the time being, have a try and let me know if you have any feedback. The calendar has been developing for quite some time and keep evolving based on the your comments.

Thank you!! I hope you enjoy the reading and sharing.

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

Saturday, March 30, 2024

Analytical Calendar - Revised PowerPoint Deck

Post Index

Analytical Calendar The Story

2024-03-30

Analytical Calendar

Revised Presentation Deck

A Deck helps to explain Analytical Calendar

This post is to provide you the revised deck to help explain about the analytical calendar. The screen captures are shown below and the corresponding PowerPoint as well as the applications, you can find in below links.

PowerPoint

https://github.com/kongson-cheung/analytical-calendar/blob/main/PowerPoint/AnalyticalCalendar_2024-03-30.pptx

QlikView Example

https://github.com/kongson-cheung/analytical-calendar/blob/main/QlikView%20Analytical%20Calendar%20Examples/AnalyticalCalendar6.0.qvf

PowerBI Example

https://github.com/kongson-cheung/analytical-calendar/blob/main/PowerBI%20Analytical%20Calendar%20Examples/AnalyticalCalendar6.0.pbix

Qlik Sense (SaaS) Example

https://github.com/kongson-cheung/analytical-calendar/blob/main/Qlik%20Sense%20Analytical%20Calendar%20Examples/Analytical%20Calendar%206.0%20SaaS.qvf

The presentation slides: