Analytical Calendar The Story | The Index Page
2023-05-16
Analytical Calendar
Analytical Calendar Implementation
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.