5 Replies Latest reply: Jan 16, 2017 8:53 AM by Andrey Khoronenko RSS

    Master Calendar

    Abu Sayeed Baseer

      Dear all

       

      I have two date fields. One in Sales_HDR as SALE_DATE and other one is Sales_RTRN_HDR as SALE_RETURN_DATE

      i want to create a master calendar using these two date fields. Can anyone please guide me. how to do that ????

       

      Thanks.

      Sayeed

        • Re: Master Calendar
          Girirajsinh Vaghela

          Below works for me to create master calendar

           

          ---------

          make your Start, End based on Sales_HDR and Sales_RTRN_HDR


          I just used following

          LET S_DATE_START = NUM(YEARSTART(ADDYEARS(TODAY(), -2)));
          LET S_DATE_END = NUM(FLOOR(MONTHEND(TODAY())));

          Calendar:
          LOAD
              
          CALENDAR_DATE AS %KEY_CALENDAR_DATE,
              
          DATE(CALENDAR_DATE) AS Date,
              
          YEAR(CALENDAR_DATE) AS Year,
              
          MonthName(date(CALENDAR_DATE)) as MonthName,
              
          MONTH(CALENDAR_DATE) as MonthShort,
              
          DUAL(DATE(CALENDAR_DATE, 'MMMM'), NUM(MONTH(CALENDAR_DATE))) AS Month,
              
          NUM(MONTH(CALENDAR_DATE)) AS Month_num,
              
          NUM#(DATE(CALENDAR_DATE, 'YYYYMM')) AS YYYYMM,

          //    DATE(WEEKSTART(CALENDAR_DATE), 'YYYYMMDD') AS YYYYMMDD,
              DAY(CALENDAR_DATE) AS Day,
          //    DUAL(WEEKDAY(CALENDAR_DATE), NUM(WEEKDAY(CALENDAR_DATE))) AS Weekday,
              'Q' & CEIL(NUM(MONTH(CALENDAR_DATE))/3) AS Quarter,
          //    DUAL(DATE(CALENDAR_DATE, 'MMM-YYYY'),
          //         NUM#(DATE(CALENDAR_DATE, 'YYYYMM'))) AS CALENDAR_YEAR_MONTH,
          //    DUAL(NUM(DAY(WEEKSTART(CALENDAR_DATE)), '00') &'/'& DATE(WEEKEND(CALENDAR_DATE), 'DD-MMM-YYYY'),
                  YEAR(WEEKSTART(CALENDAR_DATE)) &  NUM(WEEK(WEEKSTART(CALENDAR_DATE)), '00') AS WeekC

          ;
          LOAD
               (
          $(S_DATE_START) + RECNO()) - 1 AS CALENDAR_DATE
          AUTOGENERATE((S_DATE_END - S_DATE_START) + 1);

          • Re: Master Calendar
            Muñoz Héctor

            Hi Abu Sayeed Baseer,

             

            A possible solution would be joinning both fact tables into one and create a calendar from the date field, as follows:

             

            // Tables with sales data

            Sales_HDR:

            LOAD * INLINE [

            Product SHDR, Shop SHDR, Date SHDR, Sales SHDR

            P01, S1, 02/01/2017, 23

            P01, S1, 03/01/2017, 23

            P02, S1, 04/01/2017, 12

            P02, S1, 05/01/2017, 12

            P03, S1, 02/01/2017, 14

            P01, S2, 02/01/2017, 23

            P01, S2, 02/01/2017, 23

            P01, S2, 04/01/2017, 23

            P02, S2, 02/01/2017, 12

            P02, S2, 05/01/2017, 12

            P02, S2, 07/01/2017, 12

            ];

             

            // Tables with returns data

            Sales_RTRN_HDR:

            LOAD * INLINE [

            Product RTRN, Shop RTRN, Date RTRN, Sales RTRN

            P01, S1, 04/01/2017, 23

            P01, S2, 06/01/2017, 23

            P02, S2, 07/01/2017, 12

            ];

             

            // Table with concatenated data

            Sales:

            LOAD 'Sales' AS Type,

              [Product SHDR] AS Product,

              [Shop SHDR] AS Shop,

              Num([Date SHDR]) AS [Num Date],

              [Sales SHDR] AS Sales

            RESIDENT Sales_HDR;

             

            CONCATENATE (Sales)

            LOAD 'Returns' AS Type,

              [Product RTRN] AS Product,

              [Shop RTRN] AS Shop,

              Num([Date RTRN]) AS [Num Date],

              -[Sales RTRN] AS Sales

            RESIDENT Sales_RTRN_HDR;

             

            // Calendar table

            CALENDAR:

            LOAD *,

              Year([Num Date]) AS Year,

              Month([Num Date]) AS Month,

              Day([Num Date]) AS Day;

            LOAD DISTINCT [Num Date]

            RESIDENT Sales;

             

            I attach the QV app.

             

            Regards,
            Héctor

            • Re: Master Calendar
              Rob Wunderlich

              See this tutorial for one approach to handling multiple date fields with a single calendar.  The sample file is available in both Sense and QlikView versions.

               

              Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

               

              -Rob

              http://masterssummit.com

              http://qlikviewcookbook.com

              • Re: Master Calendar
                Aehman K

                Create a DateID field for both Fact and Master.

                 

                It depends on which date you want to be as Key?

                If you want

                Trim(Date(SALE_DATE,'YYYYMMDD') as DateID

                or

                Trim(Date(SALE_RETURN_DATE, 'YYYYMMDD') as DateID

                In your fact table

                And in Master Table

                Trim(Date(TempDate,'YYYYMMDD') as DateID,

                Date(TempDate,'MM/DD/YYYY') ad [Effective Date]

                Note: TempDate is temporary date field you're creating to get all calendar dates.

                It can be either and I don't think it should matter which one is Key (experts here might do it differently) but as long as you've Effective Date in place for Master.


                The above field will make a key from your Fact to Master.


                Try below Master Script


                // Date Dimension

                 

                 

                 

                // to load Quarters Full Name

                QuarterNAME:

                 

                LOAD * Inline [

                Quarter , QuarterFullName

                Q1 ,FIRST

                Q2 ,SECOND

                Q3 ,THIRD

                Q4 ,FOURTH

                ];

                 

                 

                // to create Quarters ie Q1,Q2

                QuartersMap: 

                    MAPPING LOAD  

                    rowno() as Month, 

                    'Q' & Ceil (rowno()/3)  as Quarter 

                   

                    AUTOGENERATE (12); 

                 

                 

                     

                       varMinDate = num(date(mid('2000-01-01',1,10 ),'YYYY-MM-DD'));

                //       varMaxDate = num(date('2017-01-10','YYYY-MM-DD'));

                       varMaxDate = num(date(today(),'YYYY-MM-DD'));

                 

                 

                // Creating a Temporary Calendar

                     

                TempCalendar: 

                    LOAD 

                                   $(varMinDate) + Iterno()-1 as Num, 

                                   Date($(varMinDate) + IterNo() - 1) as TempDate 

                                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

                  

                // Date Dimension

                 

                 

                MasterCalendar: 

                 

                 

                LOAD

                  *,

                  [Quarter Number]-1 as PQ,

                  if(LastQtrFlag=1 and len(QuarterEnd([Effective Date]))>0,1,0) as lastQtrMnth,

                // If(Year<=$(vLast5Year),Year,If(Year>=$(vLast5Year),Year )) as Years,

                // If (Year >= $(vLast5Year), Quarter) as Quarter_5Year,

                // If (Year >= $(vLast5Year), Month) as Month_5Year,

                 

                 

                 

                 

                  AutoNumber ([Year Month Num], 'PeriodID') as PeriodID,

                  AutoNumber (Year & Quarter, 'QuarterID') as QuarterID;

                 

                Load 

                               

                trim(date(TempDate,'YYYYMMDD')) as [DateID],

                  date(TempDate,'MM/DD/YYYY') as [Effective Date],

                 

                  if(month(QuarterEnd(TempDate))=Month(TempDate),1,0) as [LastMnthPerQtr],

                 

                    day(TempDate) as Day,

                    TempDate as [US Calendar Format],

                    date(TempDate,'DD/MM/YYYY') as [UK Calendar Format],

                    date(TempDate,'WWWW') as [Full Day Name],

                    year(TempDate) as Year,

                    inyear(TempDate,today(),0) * -1   as [CY],    // Current Year

                    inyear(TempDate,today(),-1) * -1 as [First PY],

                    inyear(TempDate,today(),-2) * -1 as [Second PY],

                    inyeartodate(TempDate,today(),0) * -1   as [CYTD],

                    inyeartodate(TempDate,today(),-1) * -1 as [First PYTD],

                    inyeartodate(TempDate,today(),-2) * -1 as [Second PYTD],

                      if(InYearToDate(TempDate, today(),0), 1, 0) as CurYearFlag,

                if(InQuarterToDate(TempDate, today(),0), 1, 0) as CurQtrFlag,

                if(InMonthToDate(TempDate, today(),0), 1, 0) as CurMonthFlag,

                if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,

                if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,

                if(InMonth(TempDate, today() ,-1), 1, 0) as LastMonthFlag,

                if(InMonthToDate(TempDate, today(),-2), 1, 0) as LastQtrLastMonthFlag,

                 

                 

                   

                    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                   

                    Ceil(Month(TempDate)/3) as [Quarter Number],

                    quarterName(TempDate) as [Quarter Name],

                    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],

                   

                    inquarter(TempDate,today(),0) * -1 as [CQ],  // Current Quarter

                    //    if(InQuarter(TempDate, today(),-1), 1, 0) as [PQ1],  // Previous Quarter

                    inquarter(TempDate,today(),-4) * -1 as [First PQ],

                    inquarter(TempDate,today(),-8) * -1 as [Second PQ], 

                    inquartertodate(TempDate,today(),0) * -1 as [CQTD],

                    inquartertodate(TempDate,today(),-4) * -1 as [First PQTD],

                    inquartertodate(TempDate,today(),-8) * -1 as [Second PQTD],

                    date(monthstart(TempDate),'MM') as [Month Number],

                    num(month(TempDate)) as Num_Month,

                    month(TempDate) as Month,                  

                    date(monthstart(TempDate),'MMMM') as [Month Full Name],

                    monthstart(TempDate) as [Calendar Month Start Date],

                    monthend(TempDate) as [Calendar Month End Date],

                  date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

                  date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

                 

                 

                    week(TempDate) as Week,

                    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

                    week(weekstart(TempDate)) & '-' & Month(TempDate)   as [Week Month],

                    weekDay(TempDate) as [Week Day],

                     

                    If( TempDate > monthstart(addmonths(today(),-11)) and TempDate <= today(),1) as [Rolling 12],

                    If( TempDate > monthstart(addmonths(today(),-2)) and TempDate <= today(),1) as [Rolling 3]

                               

                Resident TempCalendar 

                Order By TempDate ASC; 

                 

                 

                Drop Table TempCalendar;

                Drop Table  QuarterNAME;

                 


                Also check the Date format you're using Vs this script Date format which might be different.



                Thanks,


                • Re: Master Calendar
                  Andrey Khoronenko

                  Hi Sayeed,

                   

                  I recommend additionally look

                   

                  Generating Missing Data In QlikView

                   

                  Andrey