4 Replies Latest reply: Jul 26, 2016 3:09 AM by Gysbert Wassenaar RSS

    Master Calendar

    Monica Maidana

      Hi,

      I have two tables (Qlik Sense 3.0), with dates, one with historical dates and one with forecast dates.  They both start and end at different points in time.  My query is... (apologies for my ignorance, this is my first go at this) can the one master calendar look at a min date from the historical data and max date from the forecast data?  Or do I need two different calendars or some other solution?

      Thanks in advance.

      Monica

        • Re: Master Calendar
          Gysbert Wassenaar

          Yes, that's possible. Best to give the date field in both tables the same name so they will both be linked to the master calendar. After loading both tables you can generate a master calendar using the date field that now contains all the historical and forecast dates. Something like this:

           

          Temp:

          LOAD

               Date(min(FieldValue('MyDate', RecNo())) as MinDate,

               Date(max(FieldValue('MyDate', RecNo())) as MaxDate,

          AUTOGENERATE FieldValueCount('MyDate') ;

           

          Let vMinDate = peek('MinDate');

          Let vMaxDate = peek('MaxDate');

           

          DROP TABLE Temp;

           

          MasterCalendar:

          LOAD

               MyDate,

               Year(Date) as Year,

               Month(Date) as Month,

               ...etc...

          LOAD

               Date($(vMinDate) + RecNo() -1) as MyDate

          AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1 ;

          • Re: Master Calendar
            vijaykrishnamraju vegesna

            Just follow procedure like this

             

             

             

             

            OrdersCalTemp:

            Load Min(BILLDATE) as MinOrdDate,

            Max(BILLDATE) as MaxOrdDate

            Resident FACTTABLE;

             

             

            let EndDate=Date(Peek('MaxOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

            let StartDate=Date(Peek('MinOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

            let caldays=(EndDate-StartDate)+1;

            let vToday=num(EndDate);

             

             

            Cal:

            Load RecNo() as RecordNo,

            if(RecNo()=1,Date('$(StartDate)'),Date(peek("D")+1)) as D

            autogenerate(caldays);

             

             

            Calendar:

            BILLDATE,

            ........

            .......

            Resident Cal;