Let's Take a walk on the Calendar / Date Bridge: Working with Multiple Dates / Calendars

[Michael_Tarallo]

This topic has been covered before, but we have a new generation of Qlik users and I found some of the content not always easily explained. Here is my spin to help you get started working with multiple dates / calendars. Sample App and data attached!

[Michael_Tarallo]

Do you need answers for specific points in time when working with multiple calendars / dates? Then use a calendar bridge. A calendar bridge is used to create what Qlik commonly calls: a Canonical Date / a Canonical Calendar. A calendar bridge is nothing more than a simple table that links 1 or more dates to a single common date, called a canonical date. This is used to simplify time period selection during analysis and when multiple calendar / date filters can be confusing to the user.  The bridge table is linked to a key field in your data and created with a new dimension to simply describe each date type you have. (You can link this to a Master Calendar if you require more granular time periods.) Your charts can then use aggregated measures with the defined date type in a set expression to show the specific results. Watch the video below to learn more and see the attached app and sample data if you want to try it yourself. 

Want to learn more tips and tricks likes these? Don't forget to join me tomorrow 10AM ET for Set Analysis: Redux on the next Do More with Qlik Webinar

Register here

This article by our beloved HIC is a great reference with more detail if needed.

Part 2: Coming soon....

Want to learn more tips and tricks likes these? Don't forget to join me tomorrow 10AM ET for Set Analysis: Redux on the next Do More with Qlik Webinar

Register here

Multiple Calendars - Sample.qvf

[gomeri]

Hello Michale, your article is very interesting. It's possible to create master item for a variable interval of time?

For ex. I want to compare actual period (year 2023) with last period (year 2022) or with future period (year 2024).

Grazie

Giovanni O.D.

[ss2q]

where is the part 2?

[paulcalvet]

hello @gomeri 

Yes you can do that, you should have the same model.

For each date, you associated the current date and the current date - 1 year in the bridge table.

You create 2 master items

sum({<Rolling={1}>}sales) is for the selected date 

sum({<Rolling={364}>}sales) is for the selected date - 1 year

This allow you to display this year and last year in the same chart :

here is the script :

DATE1:

LOAD Distinct

Date(DateLink,'DD/MM/YYYY') as Date

FROM [$(vPathQVD_Dim)date.QVD](qvd)

;

join (DATE1)

LOAD Distinct

Date(DateLink,'DD/MM/YYYY') as Date2

FROM [$(vPathQVD_Dim)date.QVD](qvd)

;

// Last Year

M_ROLLING_CALENDAR:

LOAD Date2 as DateLink,

Date as RollingDate,

     364 as Rolling

Resident DATE1

Where Date-364 = Date2

;

// Last Month

Concatenate(M_ROLLING_CALENDAR)

LOAD Date2 as DateLink,

Date as RollingDate,

     31 as Rolling

Resident DATE1

Where Date-30 = Date2

;

drop table DATE1;

Concatenate(M_ROLLING_CALENDAR)

LOAD Distinct

Date(DateLink,'DD/MM/YYYY') as DateLink,

    Date(DateLink,'DD/MM/YYYY') as RollingDate,

    1 as Rolling   

FROM [$(vPathQVD_Dim)date.QVD](qvd)

;

NoConcatenate

M_DATE_TEMP:

LOAD Distinct

Date(DateLink,'DD/MM/YYYY') as date

FROM [$(vPathQVD_Dim)date.QVD](qvd)

;

Left join (M_DATE_TEMP)  

LOAD 

     Date(Max(date,'DD/MM/YYYY')) as MaxDate

Resident  M_DATE_TEMP;

M_CALENDAR_TEMP:

LOAD Distinct

    WeekYear(date(date,'DD/MM/YYYY')) &'W'& num(week(date(date,'DD/MM/YYYY')),'00') as [Week],

    date(date,'DD/MM/YYYY') as RollingDate,

    date(date,'DD/MM/YYYY') as Day,

    WeekDay(date(date,'DD/MM/YYYY')) as WeekDay,

    DayNumberOfYear(date) as DayNumber,

    year(date(date,'DD/MM/YYYY')) as Year,

    year(date(date,'DD/MM/YYYY'))&'-'&num(month(date(date,'DD/MM/YYYY')),'00') as [Month],

    num(year(date(date,'DD/MM/YYYY')))*12+num(month(date(date,'DD/MM/YYYY'))) as [%Key_year_month],

    WeekYear(date(date,'DD/MM/YYYY'))*52+num(week(date(date,'DD/MM/YYYY'))) as [%Key_week],

num(year(date(date,'DD/MM/YYYY')))*12+num(month(date(date,'DD/MM/YYYY')),'00') as [%Key_Date],

    MaxDate

    //day(date(date,'DD/MM/YYYY')) as Day,

    //'W'&week(date(date,'DD/MM/YYYY')) as [Week],

    //month(date(date,'DD/MM/YYYY')) as Month, 

    //num(year(date(Date,'DD/MM/YYYY')))*1000+num(month(date(Date,'DD/MM/YYYY')))*100+num(date(Date,'DD/MM/YYYY')) as [%Key_date]

Resident M_DATE_TEMP;

//where Exists(Date,date)

;

drop table M_CALENDAR_TEMP;

[Michael_Tarallo]

Hi guys - thanks for always willing to jump in - the power of the community. I am working on Part 2 as we speak - unfortunately lots of things going on with my role and responsibilities so it is difficult to always stay in sync. I will post Part 2 when completed. Stay well all. 

[Michael_Tarallo]

Hello Gomeri - you may also want to look at creating calendar measures in the master items library. - This will allow you to create a range of time period measures very easily in Qlik Sense - added to the Master Items measures.

Here is the help article for reference:
https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Measures/create-calendar... 

[paulcalvet]

Hello @Michael_Tarallo,

Do you know if you can combine the two solutions ?

Autocalendar to have the create calendar measure and the insight advisor

Pivot table to have one date in the data model, allow you to display two points for the same date with a date format like DD/MM/YYYY

Regards

[gomeri]

Hello @paulcalvet,

thank you for the very comprehensive explanation. It is very useful.

[gomeri]

@Michael_Tarallo thanks. I wait patiently for part 2. Thanks