Analytical Calendar - Concept in Brief

  Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2024-02-03

Analytical Calendar

Concept In Brief

Core Understanding of Analytical Calendar

Analytical Calendar looks like simple but also complicated.  To make sure it is easier to digest and understand, I would like to provide the concept in brief.

Let's first have a review of Analytical Calendar. Analytical Calendar consists of two calendars including Analysis Calendar and the Comparison Calendar.  Analysis Calendar is to define the analysis period while the comparison calendar is to define the comparison period.

Analytical Calendar consists of 4 core fields including:

Date Perspective

It is to define what date granularity is required for the analysis and comparison.  Without this selection, it is still possible but the Unique Date selection is difficult to filter out the required Unique Date in the list.

Unique Date

Based on the date perspective, it allows the corresponding date to be shown for selection.  For instance, If Year is selected in Date Perspective, Unique Date will show 2024, 2023, 2022, etc; if Month is selected, it shows 2024-Feb, 2024-Jan, 2023-Dec, etc.

Analysis Perspective

With the Unique Date as reference, Analysis Perspective define the period requirements such as:

- Actual

- YTD, YTM, YTW, etc

- Rolling N Year/Quarter/Month/Week/Day

Comparison Perspective

With the defined Analysis requirements, it further selects the comparison period with respect to the analysis requirements.

- N Year/Quarter/Month/Week/Day Before

- Custom Year/Quarter/Month/Week/Day

- N/A

How the Calendars Work

In Analytical Calendar, the usage is like below flow.

Analysis Period --> Comparison Period --> Calendar Control --> Fact Table Association

Analysis Period

The Analysis Period is purely defined by Date Perspective, Unique Date and Analysis Perspective.

Date Perspective and Unique Date are apparently a new method to define a date in particular granularity.  This method eliminates conflicts in human interpretation and manipulation about the selected date by date hierarchy, i.e. Year-->Month/Week-->Date.

Once the Date with Granularity is defined, next is to define the date range by Date Perspective.  The selected Unique Date is actually the date end of the date range.  If date range is having same start and end date, it is called Actual.  Other will be represented by YTD, YTM, Rolling N Year/Month/Quarter/Week/Day.

All these then construct the Analysis Calendar.

Comparison Period

Comparison Period cannot exist without the Analysis Period.  It is, in fact, a period with reference to the date range of the analysis period.  The Comparison Perspective is generalized with a standard representation in two ways.  One is to define a date range before and the other is free date selection.

There is one very special scenario for Comparison Perspective is no comparison period.  For completeness purpose, it is required for a value to be selected in the comparison period.

As a result, three categories are available.  And the Date Perspective, Unique Date and Analysis Perspective and Comparison Perspective construct the Comparison Calendar.

Calendar Control

Both analysis period and comparison period are, in fact, trying to reflect the selection in Analysis Calendar and Comparison Calendar.  The analysis period defined is tagged as ANALYSIS while the comparison period defined is tagged as COMPARISON.  If the CONTROL is not selected with a value, it means both analysis and comparison.

This CONTROL is very important in order to control which calendar to take effect.

Fact Table Associated

The Fact Table associated with both analysis and comparison periods in the form of the actual date association.   Based on the Control (ANALYSIS or COMPARISON), it allows to reflect the analysis period and comparison period.

Two examples are illustrated below.

In the first example, Date Perspective, Unique Date and Analysis Perspective first define 2023-05 for Analysis period.  And then the Comparison Perspective furthers define the Comparison period to be 2023-04.  Both Analysis Period and Comparison Period are combined into the into the Calendar Control where indicating 2023-05-01 to 2023-05-31 is ANALYSIS Period and 2023-04-01 to 2023-04-30 is COMPARISON Period.  Then the actual date range is reflected into the fact table with the range 2023-04-01 to 2023-05-31.

The second example is similar to the first example but this time, no comparison period is defined.  Thus, the Calendar Control only has ANALYSIS Period and Fact Table is only reflecting the date range from 2023-05-01 to 2023-05-31.

Summary

Analysis Calendar provide methods to select values to define the ANALYSIS period while Comparison Calendar provide methods to select values to define the COMPARISON period.  Fortunately, both calendars share the same fields including Date Perspective, Unique Date and Analysis Perspective.  This is one of the main design of Analytical Calendar and it is one reason why it can simplify the selections.  The other main concept is the Calendar Control.  It is important that there is a way to distinguish the ANALYSIS and COMPARISON periods.  Incorporating these concepts altogether, it is the Analytical Calendar.

I hope this brief description can help you understand further about the Analytical Calendar.  Next, I would share how to apply Analytical Calendar into other tools like PowerBI and Tableau.

Thank you for reading.  I hope it help you.  Just leave a message if you have any discussion/share want to make.

Analytical Calendar - Minor Enhancements

 Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2024-01-27

Analytical Calendar

Minor Enhancements

Feedback drives the New Design

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.

Every 28 (7 *4) years, the whole pattern reappear.   1 Jan is Sunday can happens in 5, 6, 11 year gap.  The 7 day week and the total number of day in year creates a very confusing situation.

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.

This problem is fixed with the additional week shift.  However, I am not able to identify a rule for this.  My method to review all the inclusive years in the comparison in order to make the shift.  If you come up with purely formula-based method, please share me.

N Day Rolling in Month End

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.

Analytical Calendar - Technical Discussion

Post Index

How to Use | Analytical Calendar The Story | The Index Page

2023-09-23

Analytical Calendar

Technical Discussion

The technical design in detail

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.

Analytical Calendar - How to Use

Post Index

Technical Discussion | Analytical Calendar The Story | The Index Page

2023-09-08

Analytical Calendar

How to Use And the Reasons

An intuitive way to perform time-intelligent analysis

Analytical Calendar is extremely useful to be used in interactive dashboard.  It is, in fact, having all the core components when manipulating date and calendar but it is re-designed to satisfy a more simplified way to benefit both the analysts and developers.

On the front-end, because of the simplicity to have just analysis perspective and comparison perspective, the presentation can be more consistent.  Instead of providing different charts for different date granularity, a single chart can already presented different period with different date granularity.  More details will be shared later in this article.

On the back-end, because now everything is already pre-calculated in the data model, it does not require complicated expression to be developed.  It greatly saves the effort for the developer.  More details will be shared later in this article.

In short, this article focuses on the demonstration of the analytical calendar to share the benefits of it.  And it also covers a bit on how it helps the analysts on selecting the date period with a few selections and how it helps developers to reduce the time and effort in design and development.

Let's start.

* this analytical calendar is a more enhanced version from my previous posts.

Two examples are shared in Github:

It is date level with complete week example and also with custom.

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

It is month level example.

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

Analytical Calendar

Analytical calendar is a composite of tables associated together to provide time intelligent experience on usage and development.  It is a generic design that can be applied in different kinds of interactive dashboard.  The below will share the core component to let you understand how it can be used.  There are four main components required user selection including:

• Date Perspective
• Unique Date
• Analysis Perspective
• Comparison Perspective

Only selecting these 4 elements, it is allowed to perform time intelligent date range selection.

Date Perspective

It is uncommon that analysis is performing in different granularity levels.  Even if it is possible, it has a lot of assumptions to make it happen.  Defining a proper date granularity, it makes the overall analysis consistent and easier.  It named as Date Perspective to make it easier to understand by general users.

Date Perspective consists of only five values including Year, Quarter, Month, Week and Date.  It is to ensure the proper date granularity of the overall analysis and more technically, it is controlling the corresponding selection to comply with the integrity.  For example,

if Year is selected,
Unique Date: 2023, 2022, 2021, etc.
Analysis Perspective: Actual, Rolling 2 Years, etc.
Comparison Perspective: 1 Year before, 2 Year before, etc.

if Quarter is selected, 
Unique Date: 2023-Q3, 2023-Q2, etc.
Analysis Perspective: Actual, YTQ, Rolling 2 Quarters, etc.
Comparison Perspective: 1 Year before, 1 Quarter before, etc.

if Month is selected, 
Unique Date: 2023-Sep, 2023-Aug, etc.
Analysis Perspective: Actual, YTM, Rolling 2 Months, etc.
Comparison Perspective: 1 Year before, 1 Quarter before, 1 Month before, etc.

if Week is selected, 
Unique Date: 2023-W41, 2023-W40, etc.
Analysis Perspective: Actual, YTW, Rolling 2 Weeks, etc
Comparison Perspective: 1 Year before, 1 Week before, etc.

if Date is selected, 
Unique Date: 2023-09-09, 2023-09-08, etc.
Analysis Perspective: Actual, YTD, MYD, Rolling 2 Days, etc.
Comparison Perspective: 1 Year before, 1 Quarter before, 1 Month before, 1 Week before, 1 Day before, etc.

In any analysis, it is important to first define the date "perspective" for the analysis.  It avoids the chance to lose focus when manipulating the date components compared to the traditional design.

Unique Date

Unique Date is to further make clear the overall analysis in selecting the exact date representation in the specific date granularity.  

In traditional design, it requires to first select Year then Quarter or Month then to Day.  For developer, it is a disaster to determine the date granularity based on the selection.  A lot of rule-based calculation is required to be defined at the expression to manipulate the selection.  And because of this, this increases the chance to make multiple chart with the sake for different date level presentation.

For general users, it generates confusion, especially, in changing the selection or the view for the analysis.  It needs first to remove the selection and then to apply the new selection.  However, Year, Quarter, Month, Week, Day are conflicting with each other, e.g. if you select a month, you should not use week or vice versa.    If the selection is overridden, there is no mechanism to alleviate the proper selection.  All are just based on the human intelligence to rectify the date component selection.

Although for advanced users, it is definitely not a problem.  The analytical calendar design still hopes to alleviate this issue by making use of the unique date.

Unique date is a union of all the date representations by granularity of Year, Quarter, Month, Week and Date.

For example,

Year: 2023, 2022, 2021, etc.
Quarter: 2023-Q3, 2023-Q2, etc.
Month: 2023-Sep, 2023-Aug, etc.
Week: 2023-W41, 2023-W40, etc.
Date: 2023-09-09, 2023-09-08, etc.

In this design, it just requires to select the exact date representation directly.  There is no human interpretation and human computation required for the correct  and accurate selection.  In short, what you select is what you get.

Analysis Perspective

Analysis, in general, is always to deal a period.  A period means a start date and an end date in the same granularity level.  There are plenty kinds of representation available (to review), it looks the combination of a date plus the range type is more commonly used.  For example, 2023-09-09 YTD, 2023-06 Rolling 6 Months, etc, i.e. a unique date plus the date range type.  To be more precise, there is a date range type called Actual.  It represents itself.

Analysis perspective also follows strictly on the date integrity.  For example, Unique Date=2023, it does not allow you to select YTD, YTM, Rolling 5 months, etc.  Only if the valid combinations fallen into the same date granularity, it allows the value for selection.  In a lot of traditional design, this is not doing very well.

Comparison Perspective

Comparison is another important element in analysis.  Instead of just looking at the current performance, it also very common to compare the historical performance to see whether it is good or bad.

The most common way to represent comparison is last year, last month, or a specific date.  To make it more generic, it becomes last X Year, last X month or a custom date.  Custom date means any unique date.  It can be Year, Quarter, Month, Week or Date. 

In the implementation, it represents X Years before, X Quarters before, X Months before, X Weeks before and X Days before.  For custom date, it is a list of date representation similar to the Unique date.  Similarly, there is date integrity applied.   For example, if the 2023 Actual, it would not allow the selection of X Months before, X Weeks before because they are not in the valid granularity level.

Front-end Design Improvement

With the capability of the Analytical Calendar, the front-end now can be more neat and clean.  The reason is because there is only two perspectives for periods, i.e. Analysis period and Comparison period.

Analysis period is the period for analysis.  It is similar to the traditional design to select year, quarter, month, week and day.   Comparison period is the period for comparison.  In traditional design it is usually incorporated into the chart and each chart will represent a kind of comparison.

By making use of the Analytical Calendar, all charts now can be possible to provide either or both analysis and comparison period.  There is no need to replicate the same chart to have each to show individual Year, Month or Week, etc for the KPI and the comparison.  All charts are on-the-fly to provide the information based on the user selection.

In other words, it provide a coherent view in terms of usage and experience.  To further let the analysts understand what is being analyzed, on the top left corner, there is a textbox showing both the selection details as well as the actual date range selected.  In this way, it is crystal clear on what analysis is being performed and what comparison is being performed across from the selection to all the charts being shown in the sheet.

Back-end Design Improvement

In order to use the Analytical Calendar, it requires only two variables named vSetAnalysisAnalysisCalendar and vSetAnalysisComparisonCalendar.  The former is used for filtering the Analysis Perspective period while the latter is used for filtering the Comparison Perspective period.

The variable vSetAnalysisAnalysisCalendar is very simple as illustrated below.  It just requires to set the %CONTROL_PERSPECTIVE equals to ANALYSIS and make sure there is no Comparison Perspective.

Similarly, the variable vSetAnalysisComparisonCalendar is also very simple as illustrated below.  It just requires to ignore the %CONTROL_PERSPECTIVE equals to ANALYSIS.

With these two variables, all the expression in the chart development is straightforward.  It just needs to apply the set analysis for the two variables.  

When requiring Analysis Perspective, the below set analysis can be used.

When requiring Comparison Perspective, the below set analysis can be used.

As the front-end is now consistent and coherent, the same expression can be applied and across all the charts.  Unless, there are specific chart would be shown, these two kinds of expressions can satisfy a majority of the use cases.  Definitely, the Analytical Calendar can also be applied to more advanced use scenarios which would not cover here.

Other Advantages

Indeed, the Analytical Calendar also has other advantages.  In designing the data model, majority of the time would fall into a situation to pre-build a data or to calculate on the fly.  The rule of thumb is always to pre-build it if possible to minimize the computation required during use.

When manipulating date, there are also a few scenarios that might not be generic for every usage.  For example, if today is 2023-09-09, should it allow the selection of 2023-Sep?  In my experience, it depends on the flavor of the end users but this becomes a question of developers for a proper expression development.  Another example is the cross year week.  There are different ways to handle the incomplete week like making it full week and count as the previous year week or the let the week to be incomplete.  This is a headache not just to developer but also the analysts using the interactive dashboards.

Thus, the design of Analytical Calendar also incorporates this element.  It allows to pre-calculate all these into the data model and do not need very complicated on-the-fly expression to calculate the results.  It can still maintain as Analysis Perspective and Comparison Perspective.  The below are some scenarios for sharing.

This concludes how to use the Analytical Calendar.  Please leave me message if further discussion is needed.

Thank you for reading.  I hope it help you.  Just leave a message if you have any discussion/share want to make.

Analytical Calendar - 009 - Analytical Calendar Implementation

Post Index

 Analytical Calendar The Story | The Index Page

2023-05-16

Analytical Calendar

Analytical Calendar Implementation

The implementation of Analytical Calendar in Qlik

The above illustrates the data model for Analytical Calendar. Technically, there are 3 tables in the design including “Analytical Calendar”, “Date Range Association” and “Custom Date”.

1.        Analytical Calendar

It is a combination of the analysis calendar and the comparison calendar. A calendar type is used to distinguish which calendar.

Analysis calendar supports all the date range selection with the date range type like Actual, YTD, MTD, YTW, Rolling, etc. The date period is directly picked by users for analysis.

Comparison calendar mainly is for date comparison. In addition to the selection related to the analysis calendar, i.e. date range type, unique date and rolling range, further selection on comparison unit and comparison range to define the comparison period.

Comparison calendar also integrates the custom date. Whatever it is selected in the analytical calendar, i.e. date range type, unique date and rolling range, a custom date is available to be selected. Once custom date is selected, the comparison unit becomes “Custom”.

User is free to select all these calendar fields while in the backend, there is a control to make sure which calendar to take effect.

2.       Custom Date

This is a table containing all the dates available in the dataset. The main function is to allow the custom date range selection.

3.       Date Range Association

It consists of all the date associations that links up to the analytical calendar to the fact table by using the association of Date Key. It uses the compression method discussed in previous section.

Data Model Working Principles

The working principle of analytical calendar is simple because it only requires picking which calendar to use, i.e. analysis calendar and comparison calendar. To be more precise, comparison calendar can be separated into comparison calendar and comparison calendar – custom.

In the analytical calendar, there are three calendar datasets including analysis calendar, comparison calendar and comparison calendar – custom.

The fields in the analytical calendar include:

·         Key fields:

o    %CUSTOM_KEY

It is used to link up the custom calendar. No matter which fields are selected in the analytical calendar, it can allow any custom dates to be selected.

 

o    %DATE_RANGE_KEY

It is the key to associate the relevant dates based on the analytical calendar design. For analysis calendar, once a combination of date range type, unique date and rolling range are selected, it will associate with meaningful period for analysis. For comparison calendar, in additional the fields required in analysis calendar, additionally, it requires comparison unit and comparison range. If comparison unit is custom, it needs the custom date to be selected in the custom date table.

 

·         For user selection to control the calendars:

o    Date Range Type

To select which date granularity and also the date range representation.

 

o    Unique Date

To select the unique date, i.e. 2022 (Year), 2022-01 (Month), 2022-01-01 (Date), etc.

 

o    Rolling Range

To select when Date Range Type is rolling. It ranges from 1 to the maximum number of rolling. If date range type is not rolling, it shows N/A.

 

o    Comparison Unit

To select the comparison unit for comparison period. It is either one of the date granularities including Y, Q, M, W, D.  The integrity is already controlled when building up the comparison calendar. In other words, it would not allow Y-Actual 2022 to have a comparison unit of M, for example.

 

o    Comparison Range

To select the comparison range for the comparison period. It starts from 1 to the maximal allowed period.

 

·         Sequence fields:

o    %UNIQUE_DATE_SEQ

The unique date sequence is to sort the order of the unique date.

 

o    %DATE_RANGE_TYPE_SEQ

The date range type sequence is to sort the order of the date range type. So, all with same date granularity can be grouped together and based on the user preference to show the preferred order of the date ranges.

 

·         Calendar selection:

o    %CALENDAR_TYPE

It has two values including ANALYSIS to indicate analysis calendar and COMPARISON to indicate comparison calendar.

Analysis calendar data sample is as illustrated below. It takes effect on the fields of:

1.        Date Range Type

2.       Unique Date

3.       Rolling Range

4.      %CALENDAR_TYPE = ‘ANALYSIS’

Comparison calendar data sample is illustrated below. It takes effect on the fields of:

1.        Date Range Type

2.       Unique Date

3.       Rolling Range

4.      Comparison Unit

5.       Comparison Range

6.       %CALENDAR_TYPE = ‘COMPARISON’

Comparison Calendar – Custom data sample is illustrated below. It takes effect on the fields of:

1.        Date Range Type

2.       Unique Date

3.       Rolling Range

4.      Comparison Unit = ‘CUSTOM’

5.       %CALENDAR_TYPE = ‘COMPARISON’

6.       Custom Date

Dashboard Development Principles

The Qlik associative data model already pre-calculates all the associations required for the calendar usage. For front-end dashboard development, it is required to control which calendar, i.e. analysis calendar or comparison calendar, takes the effect. In order to do that, there are two variables created:

·         vSetAnalysisAnalysisCalendar

The set analysis below helps to control the usage of the analysis calendar.

 

[%CALENDAR_TYPE] = {'ANALYSIS'},

[Comparison Range] =,

[Comparison Unit] =

 

·         vSetAnalysisComparisonCalendar

The set analysis below helps to control the usage of the comparison calendar.

 

[%CALENDAR_TYPE] = {'COMPARISON'}

$(=if(GetSelectedCount([Custom Date])>0, ',[Unique Date]={' & Concat(chr(39) & [Custom Date] & chr(39), ',') & '}', ''))

 

 

For a chart requires to show the analysis period, it will have an expression similar to below.

Sum({< $(vSetAnalysisAnalysisCalendar) >} Exp1)

 

For a chart requires to show the comparison period, it will have an expression similar to below.

Sum( {<$(vSetAnalysisComparisonCalendar)>} Exp1)

 

For instance, if the below fields are selected,

The corresponding results will be like below.

And the corresponding period textbox will show as below.

Dashboard Design Advantages

Using the analytical calendar, it gains the below benefits:

User Perspective

·         The dashboard is easier to understand with respect to analysis period and comparison period. It follows 100% of green-white-grey design.

·         Reduce the ambiguity in date selection and chart presentation.

·         The concept is new to the user, but it is easy to pick up and understand. Less than 10 selection attempts, user is able to pick how to use.

Developer Perspective

·         It is a generic design and able to be re-used in many dashboards.

·         It reduces the complexity of the dashboard development to write very complicated set analysis and expression.

·         No need to consider conditions of selection against the chart behavior. It follows the design to show analysis period or comparison period.

·         The design allows customization to add in more date range type.

Previous Section <== 008 - Analytical Calendar - Analytical Calendar Design

Analytical Calendar The Story | The Index Page