Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

massimofavaro
New Contributor III

Creating Master Calendar and adding related entries

Hello everyone.

I have a particular table I want to recreate.

Let me explain my current situation:

Table           [user]

Columns      [open_id]

                    [subscribe_date]

                    [unsubscribe_date]

Table           [call_log]

Columns     [open_id]                

                   [request_date]

These two tables are linked together by the field [open_id] ([user] One ---- Many [call_log]).

What I'd like to achieve is a Matrix that has Months as rows, Year as Columns and as values the number of subscriptions, unsubscriptions and number of requests.

I can easily do THREE different matrix by adding the related autocalendar date: the first matrix will have the subscriptions year and month, the second one the unsubscription year and month and the third the request year and month.

The real problem is that I don't know how to join these information together in ONE SINGLE matrix.

With another sotware I thought to create a master calendar, with minimun date of subscriptions, and as maximum date the max of the request and subscription date.

Then I joined the master table to [user] by MasterDate and Subscribe date, then i created three columns on the master calendar that counted the related rows to add the number of subscriptions. unsubscriptions and reqiests for every day in the calendar.

Is it possible to do so in Qlik Sense?
I can create a Master Calendar but I am very unsure about the calculated columns.

What should be the formula like, or, atleast, the main function to use?
The other software I'm very keen on uses a RELATED formula that isn't present in QlikSense.


Many thanks to who may help me, even a little bit!

1 Solution

Accepted Solutions
Highlighted
YoussefBelloum
Esteemed Contributor

Re: Creating Master Calendar and adding related entries

Hi,

To solve this one, you should:

  • Create a canonical date

or

  • Load the Master calendar several times

These links should help you a lot

Canonical Date

Why You sometimes should Load a Master Table several times

Linking to two or more dates

3 Replies
Highlighted
YoussefBelloum
Esteemed Contributor

Re: Creating Master Calendar and adding related entries

Hi,

To solve this one, you should:

  • Create a canonical date

or

  • Load the Master calendar several times

These links should help you a lot

Canonical Date

Why You sometimes should Load a Master Table several times

Linking to two or more dates

massimofavaro
New Contributor III

Re: Creating Master Calendar and adding related entries

It seems complex, but it seems the best way to do it...

Many thanks, I'm going to study these cases and try to apply them!

YoussefBelloum
Esteemed Contributor

Re: Creating Master Calendar and adding related entries

You're welcome

Yes, handling one date field is easier than 3 date fields

Just do it, after it will be simple to redo

Community Browser