3 Replies Latest reply: Feb 11, 2018 1:43 PM by Manish Kachhia RSS

    Link 2 Date Fields in 1 Table to 1 Master Calendar

    Twan Peters

      I have a table with 3 fields:

       

      Example_Table:

      Fact

      Closed_Date

      Respons_Date

       

      When I filter on Closed_Date I want to see the sum of Fact.

      And of course the same for the Response_Date.

      The filtering on the date fields shouldn't of course interfere each other.

       

      This works perfect with expressions like:

      =Sum( {<Closed_Date>}  Fact)

      =Sum( {<Respons_Date>}  Fact)

       

      However, eventually I want to have 1 master calendar linked to both the Date Fields. The user should be able to filter on a specific date and that date should be selected in both the Date fields.

       

      I think that for that I need to create a link table between my Example Table and the master calendar, however I'm struggling with that. Any tips?

       

      *Ps, this is an example, when i know how to create this link table i will use it in a bigger dashboard

        • Re: Link 2 Date Fields in 1 Table to 1 Master Calendar
          Anil Samineni

          PFA

           

                                                                      ***** Script *****

          Sample:

          LOAD Fact,

               Closed_Date

          FROM

          [2_Date_Fields.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          NoConcatenate

          Sample1:

          LOAD Fact,

               Respons_Date

          FROM

          [2_Date_Fields.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          CalendarMaster:

          LOAD

              Date(Closed_Date) AS Closed_Date;

          Load Date(MinDate + IterNo() -1 ) AS Closed_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

          Load

              Min(Closed_Date) AS MinDate,

              Max(Closed_Date) AS MaxDate

          RESIDENT Sample;

           

          LOAD

              Date(Respons_Date) AS Respons_Date;

          Load Date(MinDate + IterNo() -1 ) AS Respons_Date While (MinDate + IterNo() - 1) <= Num(MaxDate);

          Load

              Min(Respons_Date) AS MinDate,

              Max(Respons_Date) AS MaxDate

          RESIDENT Sample1;

          • Re: Link 2 Date Fields in 1 Table to 1 Master Calendar
            Manish Kachhia

            Use something like below.

             

            FactTable:
            LOAD Fact, 
                 Closed_Date, 
                 Respons_Date
            FROM
            [https://community.qlik.com/servlet/JiveServlet/download/1433301-314246/2_Date_Fields.xlsx]
            (ooxml, embedded labels, table is Sheet1);
            
            
            LinkTable:
            Load Distinct Fact, Closed_Date as LinkDate, 'Closed' as Flag Resident FactTable;
            Load Distinct Fact, Respons_Date as LinkDate, 'Respons' as Flag Resident FactTable;
            
            
            TempMinMaxDate:
            Load 
             Min(LinkDate) as MinDate,
             Max(LinkDate) as MaxDate
            Resident LinkTable;
            
            
            Let vMinDate = Num(Peek('MinDate',0,'TempMinMaxDate'));
            Let vMaxDate = Num(Peek('MaxDate',0,'TempMinMaxDate'));
            
            
            Drop Table TempMinMaxDate;
            
            
            Calendar:
            Load 
             Date(TempDate) as LinkDate,
             Week(TempDate) as Week,
             Month(TempDate) as Month,
             'Q' & Ceil(Month(TempDate)/3) as Quarter,
             Year(TempDate) as Year
            ;
            Load 
             $(vMinDate) + IterNo() - 1 as TempDate 
            AutoGenerate 1 
            While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
            

             

            Use flag in your expressions to distinguish between Closed and Response date.