3 Replies Latest reply: Mar 2, 2018 4:36 AM by Youssef Belloum RSS

    Creating Master Calendar and adding related entries

    Massimo Favaro

      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!