15 Replies Latest reply: Sep 27, 2011 7:48 AM by sandeepa rout RSS

    Creating a Calendar

    sandeepa rout

      Hi All,

       

      (I am using Qlikview10(unlicensed).

       

       

      I need to create a calendar which should contain year, period, month, week, startdate, Enddate. I have managed to create Year, Month, Week, Day individually.

       

      But How can I create a period or month. I have used this query.

       

      Calendar:

      LOAD

      date(yearstart(Date),'YYYY') as Year,

      date(monthstart(Date),'MMM YY') as Month,

      weekday(Date) as WeekDay,

      day(Date) as Day;

      LOAD date(recno()+date#('20021231','YYYYMMDD')) as Date

      AUTOGENERATE today()-date#('20021231','YYYYMMDD')

       

       

      Please find attached of the excel-sheet. In this excel sheet you can see there is year, week, startdate and enddate. Can I able to create month and period from this.

       

      Please suggest.

        • Creating a Calendar
          Anand Chouhan

          Hi,

           

          Do some changes in script like

           

          Calendar:

          LOAD

          date(yearstart(Date),'YYYY') as Year,

          Month(Date) as Month,

          weekday(Date) as WeekDay,

          day(Date) as Day;

          LOAD date(recno()+date#('20021231','YYYYMMDD')) as Date

          AUTOGENERATE today()-date#('20021231','YYYYMMDD')

           

          Change this line

          Month(Date) as Month,

           

          in place of

          date(monthstart(Date),'MMM YY') as Month,

           

          And i want to know what is meaning of period .

           

          Rgds

          Anand

          • Re: Creating a Calendar
            Dennis Hoogenboom

            Take a look at the atteched file.

            It also contains a macro to create a data range.

             

            Hope it is usefull for you.

              • Re: Creating a Calendar
                sandeepa rout

                Hello Dennis,

                 

                Thanks for the response.

                 

                The Qlikview I have installed my machine is not licensed one. So I can't able to open it.

                Please send me the script, so that I can refer it.

                • Re: Creating a Calendar
                  Dennis Hoogenboom

                  Based on Startdate from your Excelfile it should be something like the atteched file.

                   

                  In this way the Year and Week also will be generated based on the Startdate.
                  You are ofcourse free to use te Week and Year field from the Excel file.

                    • Re: Creating a Calendar
                      Dennis Hoogenboom

                      SET ThousandSep='.';

                      SET DecimalSep=',';

                      SET MoneyThousandSep='.';

                      SET MoneyDecimalSep=',';

                      SET MoneyFormat='€ #.##0,00;€ -#.##0,00';

                      SET TimeFormat='h:mm:ss';

                      SET DateFormat='D-M-YYYY';

                      SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

                      SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

                      SET DayNames='ma;di;wo;do;vr;za;zo';

                       

                       

                       

                       

                       

                       

                      Data:

                      LOAD //Year,

                          //Week,

                             [Start Date] as datum,

                           [Start Date],

                           [End date]

                      FROM

                      [..\..\Tests\Book1.xlsx]

                      (ooxml, embedded labels, table is ImsCalendar);

                       

                       

                       

                      StartKalender:

                      LOAD

                                Date(Floor(datum)) as datum

                      Resident Data;

                       

                       

                      Master_Calendar:

                      LET varMinDate = num(peek('datum' , 0 , 'StartKalender'));

                      LET varMaxDate = num(peek('datum' , -1 , 'StartKalender'));

                      LET vToday=num(today());

                      LET MinDate = peek('datum' , 0 , 'StartKalender');

                      LET MaxDate = peek('datum' , -1 , 'StartKalender');

                      LET vstart=date(0);

                       

                       

                      Temp_Calendar:

                      LOAD $(varMinDate) + rowno()-1                                         AS Num,

                      date($(varMinDate) + rowno()-1)                               AS TempDate

                      AUTOGENERATE $(varMaxDate)-$(varMinDate) +1;

                       

                       

                      Master_Calendar:

                       

                       

                      LOAD

                      TempDate,

                      TempDate                                                                                           AS datum,

                      week(TempDate)                                                                                 AS Week,

                      year(TempDate)                                                                                 AS Year,

                      month(TempDate)                                                                       AS Month,

                      day(TempDate)                                                                                 AS Day,

                      weekday(TempDate)                                                                       AS WeekDay,

                      'Q' & ceil(month(TempDate) / 3)                               AS Quarter,

                      date(monthstart(TempDate), 'MMM-YYYY')                     AS MonthYear,

                      date(monthstart(TempDate), 'YYYY-MM')                     AS YearMonth,

                       

                       

                      week(TempDate) & '-' & year(TempDate)                     AS WeekYear,

                      inyeartodate(TempDate, $(vToday),0)*-1                     AS CurYTDFlag,

                      inyeartodate(TempDate,$(vToday),-1) * -1           AS LastYTDFlag

                       

                       

                      RESIDENT [Temp_Calendar]

                       

                       

                      ORDER BY TempDate asc;

                       

                       

                      DROP TABLE Temp_Calendar,StartKalender;