1 Reply Latest reply: Apr 6, 2012 4:57 AM by Dennis Hoogenboom RSS

    Calendar

    chris goudy

      Having trouble getting my calendar to load, any ideas why?

       

      CALENDAR TABLE

       

      //-------------------------------------------------------------

      //Get Dates from Data

      //-------------------------------------------------------------

       

       

      DateTemp:

      LOAD (Date(Date#(YearMonth,'YYYY-MM-DD'),'DD/MM/YYYY')) as YearMonth

      RESIDENT Transactions

      ORDER BY YearMonth Asc;

       

       

      LET vMinDate = Num(Peek('YearMonth', 0, 'DateTemp'));  //LET ... evaluates the expression on the right side of the ’=’ before it is assigned to the variable

      LET vMaxDate = Num(Peek('YearMonth', -1, 'DateTemp'));    

      LET vToday = Num(today());

       

       

      //-------------------------------------------------------------

      //Temporary Calendar

      //-------------------------------------------------------------

       

       

      TempCalendar:

      LOAD

      $(vMinDate)+IterNo()-1 AS Num,

      Date($(vMinDate)+IterNo()-1) AS TempDate

      AUTOGENERATE 1 WHILE ($(vMinDate)+IterNo()-1) <= $(vMaxDate);

       

       

      //-------------------------------------------------------------

      //Master Calendar

      //-------------------------------------------------------------

       

       

      MasterCalendar:

      LEFT KEEP (Transactions)

      LOAD TempDate AS %_DateKey,

           TempDate AS %_DateKeyCA,

           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,

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

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

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

           num(month(TempDate)) as Period

      RESIDENT TempCalendar 

      ORDER BY TempDate Asc;

       

       

      Drop table TempCalendar;

      Drop table DateTemp;

       

      TRANSACTIONS TABLE

       

      Transactions:

      LOAD YearMonth,

           RamsNo,

           ScriptNo,

           Nappi9,

           Nappi6,

           PayOffice,

           MedCode,

           Pharmacist, 

           Barcode,

           ShopArea,

           ProfFee,

           CostValue as Sales,

           GrossVal,

           SepCostValue,

           Quantity

      FROM

      \\10.240.12.41\Qlikview\QVData\Live\QVD\FrontAndDispTransMaster.qvd

      (qvd)

      Where GroupCode=14;


        • Calendar
          Dennis Hoogenboom

          Hi

           

          Hard to say. What is going wrong? Do you get an error while loading?

          Does your variables get values?

           

          Btw your script doesn't really make sense to me.

          You are trying create a mastercalender bases on the field YearMonth from the Transaction table right?

          Is the field YearMonth a full date field or is it only Year and Month?

           

          And can you share your table view (Ctrl + t ) with us? As far as I can see from the script I suspect there is no connection between your Transaction table and your master calender, is there?

           

          Dennis.